Write a sql to get the last data

Question

Hello everyone, I have a Realtime table with example data:

Symbol     Date            Value
ABC     1/3/2009 03:05:01   327    -- is last data for 'ABC'
ABC     1/2/2009 03:05:01   326 
ABC     1/2/2009 02:05:01   323
ABC     1/2/2009 01:05:01   313
BBC     1/3/2009 03:05:01   458    -- is last data for 'BBC'
BBC     1/2/2009 03:05:01   454
BBC     1/2/2009 02:05:01   453
BBC     1/2/2009 01:05:01   423

Please help me to write a sql to return last data for all symbol. The result is:

Symbol     Date            Value
ABC     1/3/2009 03:05:01   327
BBC     1/3/2009 03:05:01   458

P/s: I use sql server 2005. And Realtime data is very big, please optimize the sql code.

Thanks.

Answer 1

With a big assumption that Value only increases as the Date increases...

SELECT Symbol, MAX(Date) AS Date, MAX(Value) AS Value
FROM YourTable
GROUP BY Symbol

If that assumption can't be made, then there's a problem as you have no way to uniquely identify a row. If you have an IDENTITY column for example, you would find the record for each symbol with the latest Date, and the highest ID. Without the ID field, you don't actually know which record was the latest to be inserted (if there are 2 with the same Date), so have to do something like above.

If you will never have the same Date value for a given symbol (i.e. the symbol + Date together are unique) then you can do:

SELECT s.Symbol, s.Date, s.Value
FROM YourTable s
JOIN
(
    SELECT Symbol, MAX(Date) AS LatestDate
    FROM YourTable
    GROUP BY Symbol
) s2 ON s.Symbol = s2.Symbol AND s.Date = s2.LatestDate

That will then not matter if Value only ever increases over time.

Answer 2

The Answer:

SELECT
*, 
ROW_NUMBER() OVER 
  (PARTITION BY Symbol
  ORDER BY Date DESC) Position
FROM
TableName
WHERE
Position=1

Answer 3

You can also do a subselect like this

select Symbol,
       max(Date) as Date,
       (select Value from YourTable where Symbol = T1.Symbol and Date = max(T1.Date)) as Value
from YourTable T1
group by Symbol