Have Geek Brain

Write sql to get latest data

I have a live 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 write sql to return the latest data for all characters. Result:

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

P / s: I am using sql server 2005. And the real time data is very large, please optimize the sql code.

Thanks.

+2


a source to share


3 answers


Under the big assumption that Value

only increases as you increase Date

...

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

If this assumption cannot be made, then a problem arises because you have no way of uniquely identifying the string. For example, if you have an IDENTITY column, you will find an entry for each character with the latest date and highest ID. Without the ID field, you don't actually know which record was the last one to be inserted (if there are 2 with the same date), so you need to do something like the above.



If you never have the same date value for a given character (i.e. character + date is unique together), 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

It won't matter if the value only increases over time.

+2


a source


Answer:



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


a source


You can also make a similar choice

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
0


a source






More articles:


All Articles