Write a SQL to get the last data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Write a SQL to get the last data

Author

Topic

lulu2792
Starting Member

12 Posts

Posted - 2010-05-06 : 02:58:31

Hello everyone, I have a Realtime table with primary keys are Symbol and Date. I give example data of Realtime table:


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: Realtime data is very big, please optimize the sql code.

Thanks.

lulu2792
Starting Member

12 Posts

Posted - 2010-05-06 : 03:40:15

This is my sql:


declare @Realtime table
(
	Symbol	varchar(3),
	[Date]	datetime,
	Value	int
)

insert into @Realtime
select	'ABC',     '1/3/2009 03:05:01',   327	union all   -- this day is not existed in EOD -> inserting
select	'BBC',     '1/3/2009 03:05:01',   458	union all   -- this day is not existed in EOD -> inserting
select	'ABC',     '1/2/2009 03:05:01',   326	union all   -- this day is new -> updating
select	'BBC',     '1/2/2009 03:05:01',   454	union all   -- this day is new -> updating
select	'ABC',     '1/2/2009 02:05:01',   323	union all
select	'BBC',     '1/2/2009 02:05:01',   453	union all
select	'ABC',     '1/2/2009 01:05:01',   313	union all
select	'BBC',     '1/2/2009 01:05:01',   423

SELECT Symbol, [Date], Value
From
(
	Select Symbol, [Date], Value, row_no = ROW_NUMBER() OVER (PARTITION BY Symbol ORDER BY Date DESC)
	FROM @Realtime
)r
Where r.row_no = 1

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources