Returning the latest and previous value in 1 query

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)

Returning the latest and previous value in 1 query

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-10-05 : 09:16:36

Hi

I have a table that contains read_values that I want to return the latest read along with the previous read.

My table and some of the data contained in it looks like below:

Act_Ref Read ReadID ReadDate
BEG0022 4508 100011 20100901
BEG0022 4556 100014 20100902
BEG0022 4403 100010 20100801
BEG0025 4598 100015 20100902
BEG0025 4568 100306 20100905
BEG0025 4500 100306 20100801
BEG0123 4578 100012 20100901
BEG0123 4590 100017 20100905
BEG0456 4603 100018 20100905
BEG0456 4544 100013 20100901

I only want to return the last two reads by Act_Ref using the Read_ID field, so my outcome would look something like:

Act_Ref Prev Curr
BEG0022 4508 4556
BEG0025 4568 4598
BEG0123 4578 4590
BEG0456 4544 4603

I can produce a query to get the MAX read to produce the current read, but how would I choose the previous read as well?

Any advice would be much appreciated.

Thanks in advance

Dave

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-05 : 10:14:46

I think OUTER APPLY would be your friend here.

Something like


DECLARE @foo TABLE (
	[Act_ref] VARCHAR(255)
	, [Read] INT PRIMARY KEY
	, [readID] INT
	, [readDate] DATETIME
	)
INSERT @foo 
      SELECT 'BEG0022', 4508, 100011, '20100901'
UNION SELECT 'BEG0022', 4556, 100014, '20100902'
UNION SELECT 'BEG0022', 4403, 100010, '20100801'
UNION SELECT 'BEG0025', 4598, 100015, '20100902'
UNION SELECT 'BEG0025', 4568, 100306, '20100905'
UNION SELECT 'BEG0025', 4500, 100306, '20100801'
UNION SELECT 'BEG0123', 4578, 100012, '20100901'
UNION SELECT 'BEG0123', 4590, 100017, '20100905'
UNION SELECT 'BEG0456', 4603, 100018, '20100905'
UNION SELECT 'BEG0456', 4544, 100013, '20100901'


SELECT
	dt.[act_ref] AS [act_ref]
	, prev.[prev] AS [Prev]
	, cur.[cur] AS [Curr]
FROM
	( SELECT DISTINCT [act_ref] AS [act_ref] FROM @foo ) AS dt

	CROSS APPLY (
		SELECT TOP 1
			f.[read] AS [cur]
			, f.[readDate] AS [curDate]
		FROM
			@foo AS f
		WHERE
			f.[act_ref] = dt.[act_ref]
		ORDER BY
			f.[readDate] DESC
		)	
		AS cur

	OUTER APPLY (
		SELECT TOP 1
			f.[read] AS [prev]
		FROM
			@foo AS f
		WHERE
			f.[act_ref] = dt.[act_ref]
			AND f.[readDate] < cur.[curDate]
		ORDER BY
			f.[readDate] DESC
		)	
		AS prev

NOTE -- you should normalise this table so that Act_ref is a foreign key to a action_ref table. Then you don't have to distinct anything. If you did you could replace the (SELECT DISTINCT....) derived table and the CROSS APPPLY with a simple, prefrmant GROUP BY or ROW_NUNBER() approach.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-05 : 10:16:49

This should work:


select Act_Ref, min(Read) as Prev, max(Read) as Curr
from
(
select
row_number() over (partition by Act_ref order by Read DESC) as rownum,
Act_Ref,
Read
from YourTable
) as dt
where rownum < 3
group by Act_Ref

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-05 : 10:19:45

Read is a keyword. you need to use square brackets round it.


[code]

select Act_Ref, min([Read]) as Prev, max([Read]) as Curr
from
(
select
row_number() over (partition by Act_ref order by [Read] DESC) as rownum,
Act_Ref,
[Read]
from @foo
) as dt
where rownum < 3
group by Act_Ref

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-05 : 10:26:36

quote:
Originally posted by Transact Charlie

Read is a keyword. you need to use square brackets round it.

[code]

select Act_Ref, min([Read]) as Prev, max([Read]) as Curr
from
(
select
row_number() over (partition by Act_ref order by [Read] DESC) as rownum,
Act_Ref,
[Read]
from @foo
) as dt
where rownum < 3
group by Act_Ref

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

That's right - thank you.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources