Get the first row in a table for each month on condition

Question

I have a table with many records, with columns like this:

under_symbol|quote_date|percent|type

under_symbol is a string,
quote_date is a datetime2,
percent is a float,
type is a char that can be 0 or 1

I need to get the first record in the table for a given month for each under_symbol, where percent > .19 and percent < .21 and type = 1

This returns all of them for each under_symbol, but I just need the first record of the month for each under_symbol that matches it:

SELECT * 
       [under_symbol]
      ,[quote_date]
      ,[type]
      ,[percent]
  FROM [Underlying].[dbo].[Table_EOD]
  where [percent] > .18 and [percent] < .22 and type = '1'

The problem is that I get lots of records for a given month. I just need the one which is the earliest in the month for each unique symbol, for each month, that matches those conditions.

Please show some sample data and desired results (as formatted text *not* images). — Dale K, Aug 07 '21 at 22:52
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Aug 08 '21 at 00:31
If only there was a formal language for defining a table with columns of specific data types. Then one might add comments to explain usage and other helpful details. Hmm, DDL? — HABO, Aug 08 '21 at 03:12

score 3 · Accepted Answer · answered Aug 07 '21 at 23:04

3

You could try something like this

with month_cte as (
    select *, row_number() over (partition by eomonth(quote_date) order by quote_date) rn
    from [Underlying].[dbo].[Table_EOD]
    where [percent] > .18
          and [percent] < .22 
          and type = '1')
SELECT [under_symbol]
      ,[quote_date]
      ,[type]
      ,[percent]
  FROM month_cte
  where rn=1;

answered Aug 07 '21 at 23:04

SteveC

5,955
2
11
24

SteveC, your code works, but there is one problem, and I need to change the question because I realize I am not being clear, I need the first row for each under_symbol that matches that condition. – Ivan Aug 07 '21 at 23:12
Actually, I think just adding underlying_symbol to the partition statement does the trick. – Ivan Aug 08 '21 at 00:08

score 1 · Answer 2 · answered Aug 08 '21 at 01:44

select *, 
row_number() over (partition by [month field], under_symbol order by [month field]) as first_record
from [TABLE NAME]
where percent > .18 and percent < .22 
    QUALIFY  first_record = 1 #if this part doesn't work try to do it in a CTE/ seperate sub-query

Get the first row in a table for each month on condition

2 Answers2