Tuesday, 8 March 2011

Select latest data from table

DECLARE @TEMP TABLE( 
 Ticker varchar(50), 
 Date int,
 Details varchar(50)
)
INSERT INTO @temp
SELECT 'A',20110101,'A-1'
UNION SELECT 'B',20110101,'B-1'
UNION SELECT 'A',20110102,'A-2'
UNION SELECT 'C',20110102,'C-2'
--Select whole universe
SELECT * FROM @temp a
ORDER BY a.Ticker
--Select latest data (can't include Details)
SELECT a.Ticker,MAX(a.Date) Date
FROM @temp a
GROUP BY a.Ticker
ORDER BY a.Ticker
--Select data with Details (no longer latest)
SELECT a.Ticker,MAX(a.Date) Date,Details
FROM @temp a
GROUP BY a.Ticker,Details
ORDER BY a.Ticker
--Select latest data (with Details)
SELECT a.Ticker,a.Details,a.Date
FROM @temp a inner join @temp b ON a.Ticker = b.Ticker
GROUP BY a.Ticker, a.Details, a.Date
HAVING a.Date = MAX(b.Date)
ORDER BY a.Ticker

No comments:

Post a Comment