Getting back the max record for multiple records

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)

Getting back the max record for multiple records

Author

Topic

j2dna
Starting Member

11 Posts

Posted - 2009-08-26 : 11:23:10

I've got a scenario where I'm needing to pull back the max sequence number of multiple call records. Here's an example:

Select DateTime, CallKey, CallKeySequenceNumber, ANI, DNIS, Label
From Table1
Where DateTime > '8-26-2009'
and CallKey in (1674,1664,1698,1676,1650,1644,1630)

Each record is a different call leg, so max(CallKeySequenceNumber) is going to give me the same as just CallKeySequenceNumber. What I need is if there are 2 records for Call Key 1674, one with CallKeySequenceNumber of 1 and one with CallKeySequenceNumber of 2, I only want to pull back the one with CallKeySequenceNumber of 2. If I was going to run this off of 1 CallKey I would be fine, but I need to search through multiple CallKeys (why I have CallKey in (1674,1664,1698,1676,1650,1644,1630)).

I've messed around with subqueries, but haven't gotten it to work.

Hope I'm explaining this clearly...thx in advance.

Sean_B
Posting Yak Master

111 Posts

Posted - 2009-08-26 : 11:42:42

hi,

are you looking for something like this ?
Select t1.*
From Table1 t1
CROSS APPLY (SELECT TOP 1 * FROM table1 t2
WHERE t2.DATE = t1.DATE
AND t2.callkey = t1.callkey
ORDER BY t2.CallKeySequenceNumber DESC) x
Where t1.Date > '25/08/2009'
and t1.CallKey in (1674,1664,1698,1676,1650,1644,1630)
AND t1.CallKeySequenceNumber = x.CallKeySequenceNumber

Sean

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-08-26 : 12:18:41

[code]
SELECT [DateTime], CallKey, CallKeySequenceNumber, ANI, DNIS, Label
FROM
(
SELECT [DateTime], CallKey, CallKeySequenceNumber, ANI, DNIS, Label
,ROW_NUMBER() OVER (PARTITION BY CallKey ORDER BY CallKeySequenceNumber DESC) AS RowNum
FROM Table1
WHERE [DateTime] > '20090826'
AND CallKey IN (1674,1664,1698,1676,1650,1644,1630)
) D
WHERE RowNum = 1
[/code]

j2dna
Starting Member

11 Posts

Posted - 2009-08-26 : 14:21:13

quote:
Originally posted by Sean_B

hi,

are you looking for something like this ?
Select t1.*
From Table1 t1
CROSS APPLY (SELECT TOP 1 * FROM table1 t2
WHERE t2.DATE = t1.DATE
AND t2.callkey = t1.callkey
ORDER BY t2.CallKeySequenceNumber DESC) x
Where t1.Date > '25/08/2009'
and t1.CallKey in (1674,1664,1698,1676,1650,1644,1630)
AND t1.CallKeySequenceNumber = x.CallKeySequenceNumber

Sean

I get the following error when I run this...

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'APPLY'.
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'ORDER'.

j2dna
Starting Member

11 Posts

Posted - 2009-08-26 : 14:21:59

quote:

Originally posted by Ifor


SELECT [DateTime], CallKey, CallKeySequenceNumber, ANI, DNIS, Label
FROM
(
	SELECT [DateTime], CallKey, CallKeySequenceNumber, ANI, DNIS, Label
		,ROW_NUMBER() OVER (PARTITION BY CallKey ORDER BY CallKeySequenceNumber DESC) AS RowNum
	FROM Table1
	WHERE [DateTime] > '20090826'
		AND CallKey IN (1674,1664,1698,1676,1650,1644,1630)
) D
WHERE RowNum = 1

...and I get this error when I run this.

Server: Msg 195, Level 15, State 10, Line 5
'ROW_NUMBER' is not a recognized function name.

j2dna
Starting Member

11 Posts

Posted - 2009-08-28 : 13:30:17

Anyone else have any ideas???

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-08-28 : 13:33:00

run this

select @@version

and post the output.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources