I need to get first and last record (ordered by Date
column) from table for certain SSID
. It is not a problem if there is more records with same max or min date. All I need is union all
.
I am getting last record having max(date)
with:
with c as ( select *, rnk = rank() over (partition by Date order by Date ASC) from table where SSID = '00921834800' ) select top 1 Date, City, Title from c order by Date desc
How to I get first record (min(Date)
) as well (same thing only with order by Date asc
) with single select and without using ranking again?
I’m using MSSQL 2017.
Advertisement
Answer
; with c as ( select *, rnk = rank() over (partition by Date order by Date ASC), rnk2 = rank() over (partition by Date order by Date desc) from table where SSID= '00921834800' ) select Date, City, Title from c where rnk = 1 or rnk2 = 1 order by Date desc