CODEWITHSUNDEEP
sqlsql-server
yoursweater
yoursweater

Reputation: 2041

Get most recent result for each item in a list?

Say I have a table structure with fake data like so:

id | time_created | result_data
---+--------------+-------------   
A  | 3/1/2020     | 123
A  | 4/10/2020    | 456
B  | 5/3/2020     | 789
B  | 2/5/2020     | 101

I can't figure out how to

  1. get all unique ID's and then
  2. get the most recent (i.e., top 1 most recent) result_data for that entry.

In order words, using the example data, I'd like to get the most recent version of A (the 4/10/2020 data) and the most recent version of B (5/3/2020 version) i.e. rows 2 and 3.

This query assumes I don't know the unique ID's in advance, so I'll have to have something like WHERE id IN (SELECT DISTINCT id FROM mytable) But how would you actually make this work?

Upvotes: 0

Views: 60

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use corelated subquery

select *
from table a
where time_created = (
    select max(time_created)
    from table b
    where a.id = b.id
)

or using row_number()

select b.*
from (
    select a.*
        , row_number() over (partition by id order by time_created desc) rn
    from table a
) b
where b.rn = 1

Upvotes: 2

Somendra Kanaujia
Somendra Kanaujia

Reputation: 824

You can use a CTE:

;WITH CTE AS
(
    SELECT ID, MAX(TIME_CREATED) TIME_CREATED
    FROM MYTABLE GROUP BY ID
)
SELECT A.ID, A.TIME_CREATED, A.RESULT_DATA
FROM MYTABLE A
INNER JOIN CTE ON A.ID = CTE.ID AND A.TIME_CREATED = CTE.TIME_CREATED

Upvotes: 1

zealous
zealous

Reputation: 7503

Try with window function row_number.

select
    id,
    time_created,
    result_date
from
(
    select
        *,
        row_number() over (partition by id order by time_created desc) as rn
    from yourTable
) val
where rn = 1

Your inner query produce following result using row_number

id | time_created | result_data| rn
---+--------------+------------|---   
A  | 4/10/2020    | 456        | 1  
A  | 3/1/2020     | 123        | 2
B  | 2/5/2020     | 101        | 1
B  | 5/3/2020     | 789        | 2

and in your outer query you are selecting only most recent records by selecting rn =1

Upvotes: 1

Related Questions