CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
Hrvoje
Hrvoje

Reputation: 15152

Get last and first record using rank()

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.

Upvotes: 1

Views: 9326

Answers (3)

Moulitharan M
Moulitharan M

Reputation: 749

; 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

Upvotes: 5

SQLpro
SQLpro

Reputation: 5113

One other solution is :

with 
c as 
(
select *, 
       rank() over (partition by Date order by Date ASC) AS RNK,
       count() OVER (partition by Date) AS CNT
from   table
where  SSID= '00921834800')
select Date, City, Title 
from   c
WHERE  RNK = 1
   OR  CNT = RNK
order by Date desc

Upvotes: 2

Salman Arshad
Salman Arshad

Reputation: 272066

I would use the following query:

select * from (select top 1 with ties * from t where ssid = '00921834800' order by date) as a
union all
select * from (select top 1 with ties * from t where ssid = '00921834800' order by date desc) as b

Upvotes: 1

Related Questions