Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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
like image 796
Muhammad Rehan Saeed Avatar asked Jul 21 '16 13:07

Muhammad Rehan Saeed


People also ask

How do I get the first row and last row in SQL?

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?

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 ).

What does ROW_NUMBER () over partition by do?

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.

What is ROW_NUMBER () and partition by in oracle?

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.


1 Answers

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
like image 155
Vamsi Prabhala Avatar answered Sep 19 '22 12:09

Vamsi Prabhala