CODEWITHSUNDEEP
sqlsql-servert-sql
tmpacifitech
tmpacifitech

Reputation: 609

How can I get closed date from today with sorting?

I want to sort a SQL table by closest date from today.

Heres an example table.

ID          Name          EVENTDATE (datetime)
1           test          2019-07-01 00:00:00
2           test          2020-03-21 00:00:00
3           test          2024-01-01 00:00:00
4           test1         2016-02-14 00:00:00
5           test2         2017-05-03 00:00:00
6           test3         2014-03-23 00:00:00

The expected table I want by sorting is like this.

ID          Name          EVENTDATE (datetime)
2           test          2020-03-21 00:00:00
5           test2         2017-05-03 00:00:00
4           test1         2016-02-14 00:00:00
6           test3         2014-03-23 00:00:00

As you can see, it looks like descending sort.

Yes, in normal its a descending sort.

But please see the above three rows with the same Name value test.

test value has three EVENTDATEs. 2019-07-01 00:00:00, 2020-03-21 00:00:00, 2024-01-01 00:00:00.

In this case only one row is selected by the nearest day from today. (2019-07-01 is already passed and 2020-03-21 is the nearest day)

Hope you understand what I want. (sorry for my bad English :D)

How can I handle this with SQL?

Thanks.

Upvotes: 0

Views: 166

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You want to filter as well as sort. Here is one method:

select t.*
from (select t.*,
             row_number() over (partition by id order by abs(datediff(day, eventdate, getdate()))) as seqnum
      from t
     ) t
where seqnum = 1
order by t.eventdate desc;

Upvotes: 2

Related Questions