Get last and first record using rank()

Question

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.

Answer 1

; 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