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
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
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
To get the first and last record, use UNION. LIMIT is also used to get the number of records you want.
How do I SELECT the first and last row in a GROUP BY in SQL? To do that, you can use the ROW_NUMBER() function. In OVER() , you specify the groups into which the rows should be divided ( PARTITION BY ) and the order in which the numbers should be assigned to the rows ( ORDER BY ).
PARTITION BY The ROW_NUMBER() method is then applied to each partition, which assigns a separate rank number to each partition. If the partition by clause is not specified, the ROW NUMBER function will treat the entire result as a single partition and rank it from top to bottom.
Introduction to Oracle ROW_NUMBER() function The ROW_NUMBER() is an analytic function that assigns a sequential unique integer to each row to which it is applied, either each row in the partition or each row in the result set.
This can be done using window functions min
and max
.
select distinct name,
min(timestamp) over(partition by name), max(timestamp) over(partition by name)
from tablename
Example
Edit: Based on the comments
select t.name,t.value,t1.earliest,t1.latest
from t
join (select distinct name,
min(tm) over(partition by name) earliest, max(tm) over(partition by name) latest
from t) t1 on t1.name = t.name and t1.latest = t.tm
Edit: Another approach is using the first_value
window function, which would eliminate the need for a sub-query and join.
select distinct
name,
first_value(value) over(partition by name order by timestamp desc) as latest_value,
min(tm) over(partition by name) earliest,
-- or first_value can be used
-- first_value(timestamp) over(partition by name order by timestamp)
max(tm) over(partition by name) latest
-- or first_value can be used
-- first_value(timestamp) over(partition by name order by timestamp desc)
from t
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With