CODEWITHSUNDEEP
sql
errx
errx

Reputation: 1791

select values that having maximum timestamp

I have a table:

id|val|updated
1 | 31|2012-01-12
1 | 34|2012-01-15
2 | 41|2012-01-15
3 | 44|2012-01-15
4 | 66|2012-01-01

And i need to select id and val with maximum updated value. So for this table result should be:

1| 34| 2012-01-15
2| 41| 2012-01-15
3| 44| 2012-01-15
4| 66| 2012-01-01

Upvotes: 4

Views: 17913

Answers (3)

Michael Berkowski
Michael Berkowski

Reputation: 270607

This requires a MAX() aggregate in a subquery joined against the main table.

SELECT
  tbl.id,
  tbl.val,
  tbl.updated
FROM tbl JOIN (
  /* Subquery gets MAX(updated) per id  to JOIN against */
  SELECT 
    id,
    MAX(updated) as updated
  FROM tbl
  GROUP BY id
) t2 ON tbl.id = t2.id AND tbl.updated = t2.updated

Upvotes: 6

Luke101
Luke101

Reputation: 65248

the selected answer will work just fine but here is a faster solution. It does not use joins are groups which will slow the query down. This one uses partitions which are much faster than groups and joins. Check it out:

select id, val, Maxdate as updated from
(
     select id, val,
            Max(updated) over(partition by id order by updated desc) as MaxDate, 
            Row_Number() over(partition by id order by updated desc) as RowNumber,
     from table1
)
where RowNumber = 1

Upvotes: 1

J Cooper
J Cooper

Reputation: 4998

A couple other 'portable' approaches. In my very quick test on SQL SERVER 2008, Michael's query performed best

SELECT T1.*
FROM yourTable AS T1
WHERE NOT EXISTS(
    SELECT *
    FROM yourTable AS T2
    WHERE T2.id = T1.id
      AND T2.updated > T1.updated
)

SELECT T1.*
FROM yourTable AS T1
LEFT JOIN yourTable AS T2 ON
(
         T2.id = T1.id 
    AND  T2.updated > T1.updated
)
WHERE T2.id IS NULL

Upvotes: 3

Related Questions