Get last and first record using rank()-CodePudding

I need to get first and last record (ordered by Date column) from table for certain SSID.It's not a problem if there is more records with same max or min date. All I need is union all.

I'm getting last record (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.

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

; 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