Fetch rows such that unique `date` values are equal to some value-CodePudding

I want to limit rows upto "unique date values" is equal to some value. that means I want to fetch data of 7 days.

For example, data is

1  1/2/2022  9:30  
2  1/2/2022  9:30  
3  2/2/2022  9:30  
4  3/2/2022  9:30  
5  4/2/2022  9:30

Here I want to fetch data upto 3 unique days

so expected output is

1  1/2/2022  9:30  
2  1/2/2022  9:30  
3  2/2/2022  9:30  
4  3/2/2022  9:30

Any ideas what could be possible query syntax for such conditional limit?

CodePudding user response：

Using dense_rank

select id,date_col
from (
  select *, dense_rank() over(order by date_col) r
  from (
    values
      (1, cast('2022-02-01 09:30' as datetime))
    , (2, '2022-02-01 09:30')
    , (3, '2022-02-02 09:30')
    , (4, '2022-02-03 09:30')
    , (5, '2022-02-04 09:30')
  ) d (id,date_col)
) t
where r <= 3

CodePudding user response：

A where clause is typically how rows are filtered e.g.:

select
*
from (
values
  (1, cast('2022-02-01 09:30' as datetime))
, (2, '2022-02-01 09:30')
, (3, '2022-02-02 09:30')
, (4, '2022-02-03 09:30')
, (5, '2022-02-04 09:30')
) d (id,date_col)
WHERE DATE_COL IN ('2022-02-01 09:30', '2022-02-02 09:30','2022-02-03 09:30' )

rsult

 id | date_col               
 -: | :----------------------
  1 | 2022-02-01 09:30:00.000
  2 | 2022-02-01 09:30:00.000
  3 | 2022-02-02 09:30:00.000
  4 | 2022-02-03 09:30:00.000

db<>fiddle here

The syntax used in the predicate needs to suit the data type of the column in question.