Muhammad Rehan Saeed Muhammad Rehan Saeed - 4 months ago 15
SQL Question

Getting the First and Last Row Using ROW_NUMBER and PARTITION BY

Sample Input



Name | Value | Timestamp
-----|-------|-----------------
One | 1 | 2016-01-01 02:00
Two | 3 | 2016-01-01 03:00
One | 2 | 2016-01-02 02:00
Two | 4 | 2016-01-03 04:00


Desired Output



Name | Value | EarliestTimestamp | LatestTimestamp
-----|-------|-------------------|-----------------
One | 2 | 2016-01-01 02:00 | 2016-01-02 02:00
Two | 4 | 2016-01-01 03:00 | 2016-01-03 04:00


Attempted Query



I am trying to use
ROW_NUMBER()
and
PARTITION BY
to get the latest
Name
and
Value
but I would also like the earliest and latest
Timestamp
value:

SELECT
t.Name,
t.Value,
t.????????? AS EarliestTimestamp,
t.Timestamp AS LatestTimestamp
FROM
(SELECT
ROW_NUMBER() OVER (PARTITION BY Name ORDER BY TIMESTAMP DESC) AS RowNumber,
Name,
Value
Timestamp) t
WHERE t.RowNumber = 1

vkp vkp
Answer

This can be done using window functions min and max.

select distinct name, 
max(value) over(partition by name), 
min(timestamp) over(partition by name), max(timestamp) over(partition by name)
from tablename

Example