Max records in group - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Max records in group

Author

Topic

webforyou
Starting Member

15 Posts

Posted - 2007-08-29 : 12:23:20

Hello everyone,
Could we select the max records in each group in a table depends on two columns.
Ex:
The list group by colum "Policy"
The first criteria: Max of column "lPrintQueue"
The second criteria: Max of column "nTerm"

Sample Data:
lPolicy lPrintQueue nTerm dSurrValue
112576 13708330 7 102590000
112576 13708330 8 123335000
112576 13708330 9 145314000
112576 13708330 10 168611000
112576 13708330 11 193312000
112576 13708330 12 219515000
112576 13708330 13 247330000
112576 13708330 14 276879000
112576 13708330 15 308302000
112576 13708330 16 341759000
112576 13708330 17 377432000
112576 13708330 18 415528000
112576 13708330 19 456290000
112576 13708330 20 500000000
112576 13627273 7 102590000
112576 13627273 8 123335000
112576 13627273 9 145314000
112576 13627273 10 168611000
112576 13627273 11 193312000
112576 13627273 12 219515000
112576 13627273 13 247330000
112576 13627273 14 276879000
112576 13627273 15 308302000
112576 13627273 16 341759000
112576 13627273 17 377432000
112576 13627273 18 415528000
112576 13627273 19 456290000
112576 13627273 20 500000000
112576 109922 1 0
112576 109922 2 16343000
112576 109922 3 31998000
112576 109922 4 48541000
112576 109922 5 66031000
112576 109922 6 84534000
112576 109922 7 104123000
112576 109922 8 124870000
112576 109922 9 146852000
112576 109922 10 170152000
112576 109922 11 194857000
112576 109922 12 221063000
112576 109922 13 248881000
112576 109922 14 278434000
112576 109922 15 309862000
112576 109922 16 343323000
112576 109922 17 379000000
112576 109922 18 417101000
112576 109922 19 457869000
112576 109922 20 501805000
112900 14708400 7 102590000
112900 14708400 8 123335000
112900 14708400 9 145314000
112900 14708400 10 168611000
112900 14708400 11 193312000
112900 14708400 12 219515000
112900 14708400 13 247330000
112900 14708400 14 276879000
112900 14708400 15 308302000
112900 14708400 16 341759000
112900 14708400 17 377432000
112900 14708400 18 415528000
112900 14708400 19 456290000
112900 14708400 20 500000000
112900 14708400 7 102590000
112900 14708400 8 123335000
112900 14708400 9 145314000
112900 14708400 10 168611000
112900 14708400 11 193312000
112900 14708400 12 219515000
112900 14708400 13 247330000
112900 14708400 14 276879000
112900 14708400 15 308302000
112900 14708400 16 341759000
112900 14708400 17 377432000
112900 14708400 18 415528000
112900 14708400 19 456290000
112900 14708400 20 500000000
112900 109933 7 102590000
112900 109933 8 123335000
112900 109933 9 145314000
112900 109933 10 168611000
112900 109933 11 193312000
112900 109933 12 219515000
112900 109933 13 247330000
112900 109933 14 276879000
112900 109933 15 308302000
112900 109933 16 341759000
112900 109933 17 377432000
112900 109933 18 415528000
112900 109933 19 456290000
112900 109933 20 500000000
112900 109933 7 102590000
112900 109933 8 123335000
112900 109933 9 145314000
112900 109933 10 168611000
112900 109933 11 193312000
112900 109933 12 219515000
112900 109933 13 247330000
112900 109933 14 276879000
112900 109933 15 308302000
112900 109933 16 341759000
112900 109933 17 377432000
112900 109933 18 415528000
112900 109933 19 456290000
112900 109933 20 500000000
112900 890 7 102590000
112900 890 8 123335000
112900 890 9 145314000
112900 890 10 168611000
112900 890 11 193312000
112900 890 12 219515000
112900 890 13 247330000
112900 890 14 276879000
112900 890 15 308302000
112900 890 16 341759000
112900 890 17 377432000
112900 890 18 415528000
112900 890 19 456290000

Result:
lPolicy lPrintQueue nTerm dSurrValue
112576 13708330 20 500000000
112900 14708400 20 500000000

Thanks!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-29 : 12:33:21

[code]

Declare @T Table (lPolicy int, lPrintQueue int, nTerm int, dSurrValue int)
Insert into @T Values (112576,13708330,7,102590000)
Insert into @T Values (112576,13708330,8,123335000)
Insert into @T Values (112576,13708330,9,145314000)
Insert into @T Values (112576,13708330,10,168611000)
Insert into @T Values (112576,13708330,11,193312000)
Insert into @T Values (112576,13708330,12,219515000)
Insert into @T Values (112576,13708330,13,247330000)
Insert into @T Values (112576,13708330,14,276879000)
Insert into @T Values (112576,13708330,15,308302000)
Insert into @T Values (112576,13708330,16,341759000)
Insert into @T Values (112576,13708330,17,377432000)
Insert into @T Values (112576,13708330,18,415528000)
Insert into @T Values (112576,13708330,19,456290000)
Insert into @T Values (112576,13708330,20,500000000)
Insert into @T Values (112576,13627273,7,102590000)
Insert into @T Values (112576,13627273,8,123335000)
Insert into @T Values (112576,13627273,9,145314000)
Insert into @T Values (112576,13627273,10,168611000)
Insert into @T Values (112576,13627273,11,193312000)
Insert into @T Values (112576,13627273,12,219515000)
Insert into @T Values (112576,13627273,13,247330000)
Insert into @T Values (112576,13627273,14,276879000)
Insert into @T Values (112576,13627273,15,308302000)
Insert into @T Values (112576,13627273,16,341759000)
Insert into @T Values (112576,13627273,17,377432000)
Insert into @T Values (112576,13627273,18,415528000)
Insert into @T Values (112576,13627273,19,456290000)
Insert into @T Values (112576,13627273,20,500000000)
Insert into @T Values (112576,109922,1,0)
Insert into @T Values (112576,109922,2,16343000)
Insert into @T Values (112576,109922,3,31998000)
Insert into @T Values (112576,109922,4,48541000)
Insert into @T Values (112576,109922,5,66031000)
Insert into @T Values (112576,109922,6,84534000)
Insert into @T Values (112576,109922,7,104123000)
Insert into @T Values (112576,109922,8,124870000)
Insert into @T Values (112576,109922,9,146852000)
Insert into @T Values (112576,109922,10,170152000)
Insert into @T Values (112576,109922,11,194857000)
Insert into @T Values (112576,109922,12,221063000)
Insert into @T Values (112576,109922,13,248881000)
Insert into @T Values (112576,109922,14,278434000)
Insert into @T Values (112576,109922,15,309862000)
Insert into @T Values (112576,109922,16,343323000)
Insert into @T Values (112576,109922,17,379000000)
Insert into @T Values (112576,109922,18,417101000)
Insert into @T Values (112576,109922,19,457869000)
Insert into @T Values (112576,109922,20,501805000)
Insert into @T Values (112900,14708400,7,102590000)
Insert into @T Values (112900,14708400,8,123335000)
Insert into @T Values (112900,14708400,9,145314000)
Insert into @T Values (112900,14708400,10,168611000)
Insert into @T Values (112900,14708400,11,193312000)
Insert into @T Values (112900,14708400,12,219515000)
Insert into @T Values (112900,14708400,13,247330000)
Insert into @T Values (112900,14708400,14,276879000)
Insert into @T Values (112900,14708400,15,308302000)
Insert into @T Values (112900,14708400,16,341759000)
Insert into @T Values (112900,14708400,17,377432000)
Insert into @T Values (112900,14708400,18,415528000)
Insert into @T Values (112900,14708400,19,456290000)
Insert into @T Values (112900,14708400,20,500000000)
Insert into @T Values (112900,14708400,7,102590000)
Insert into @T Values (112900,14708400,8,123335000)
Insert into @T Values (112900,14708400,9,145314000)
Insert into @T Values (112900,14708400,10,168611000)
Insert into @T Values (112900,14708400,11,193312000)
Insert into @T Values (112900,14708400,12,219515000)
Insert into @T Values (112900,14708400,13,247330000)
Insert into @T Values (112900,14708400,14,276879000)
Insert into @T Values (112900,14708400,15,308302000)
Insert into @T Values (112900,14708400,16,341759000)
Insert into @T Values (112900,14708400,17,377432000)
Insert into @T Values (112900,14708400,18,415528000)
Insert into @T Values (112900,14708400,19,456290000)
Insert into @T Values (112900,14708400,20,500000000)
Insert into @T Values (112900,109933,7,102590000)
Insert into @T Values (112900,109933,8,123335000)
Insert into @T Values (112900,109933,9,145314000)
Insert into @T Values (112900,109933,10,168611000)
Insert into @T Values (112900,109933,11,193312000)
Insert into @T Values (112900,109933,12,219515000)
Insert into @T Values (112900,109933,13,247330000)
Insert into @T Values (112900,109933,14,276879000)
Insert into @T Values (112900,109933,15,308302000)
Insert into @T Values (112900,109933,16,341759000)
Insert into @T Values (112900,109933,17,377432000)
Insert into @T Values (112900,109933,18,415528000)
Insert into @T Values (112900,109933,19,456290000)
Insert into @T Values (112900,109933,20,500000000)
Insert into @T Values (112900,109933,7,102590000)
Insert into @T Values (112900,109933,8,123335000)
Insert into @T Values (112900,109933,9,145314000)
Insert into @T Values (112900,109933,10,168611000)
Insert into @T Values (112900,109933,11,193312000)
Insert into @T Values (112900,109933,12,219515000)
Insert into @T Values (112900,109933,13,247330000)
Insert into @T Values (112900,109933,14,276879000)
Insert into @T Values (112900,109933,15,308302000)
Insert into @T Values (112900,109933,16,341759000)
Insert into @T Values (112900,109933,17,377432000)
Insert into @T Values (112900,109933,18,415528000)
Insert into @T Values (112900,109933,19,456290000)
Insert into @T Values (112900,109933,20,500000000)
Insert into @T Values (112900,890,7,102590000)
Insert into @T Values (112900,890,8,123335000)
Insert into @T Values (112900,890,9,145314000)
Insert into @T Values (112900,890,10,168611000)
Insert into @T Values (112900,890,11,193312000)
Insert into @T Values (112900,890,12,219515000)
Insert into @T Values (112900,890,13,247330000)
Insert into @T Values (112900,890,14,276879000)
Insert into @T Values (112900,890,15,308302000)
Insert into @T Values (112900,890,16,341759000)
Insert into @T Values (112900,890,17,377432000)
Insert into @T Values (112900,890,18,415528000)
Insert into @T Values (112900,890,19,456290000)

Select T1.*
from @T T1
JOIN (
select T.lPolicy, Max(T.lPrintQueue) TMP , Max(t.nTerm) TMT
from @T T
group by lPolicy
) TMax ON T1.lPolicy = TMax.lPolicy AND T1.lPrintQueue = TMax.TMP AND T1.nTerm = TMax.TMT
GROUP BY T1.lPolicy, T1.lPrintQueue , T1.nTerm , T1.dSurrValue

[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 12:46:25

The two MAXes are not guaranteed to come from same record.

Remove these two lines from the test data


lPolicy	lPrintQueue	nTerm	dSurrValue
112900	14708400	20	500000000

And see the result.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 12:51:57

[code]SELECT DISTINCT
t.lPolicy,
t.lPrintQueue,
t.nTerm,
t.dSurrValue
FROM @T AS t
WHERE STR(t.lPrintQueue) + '|' + STR(t.nTerm) = (SELECT TOP 1 STR(w.lPrintQueue) + '|' + STR(w.nTerm) FROM @T AS w WHERE w.lPolicy = t.lPolicy ORDER BY w.lPrintQueue DESC, w.nTerm DESC)[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 12:56:53

This is off-topic because this is a SQL Server 2005 way

SELECT	lPolicy,
	lPrintQueue,
	nTerm,
	dSurrValue
FROM	(
		SELECT	lPolicy,
			lPrintQueue,
			nTerm,
			dSurrValue,
			ROW_NUMBER() OVER (PARTITION BY lPolicy ORDER BY lPrintQueue DESC, nTerm DESC) AS RecID
		FROM	@T
	) AS d
WHERE	RecID = 1

But it is twice as fast.

E 12°55'05.25"
N 56°04'39.16"

webforyou
Starting Member

15 Posts

Posted - 2007-08-29 : 13:04:11

Thanks Dinakar and Peso, your script sovle my problem, but I do not understand what Peso said.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 13:08:15

1) Run the example code [posted by Dinakar] once.
2) Remove the two lines where lPolicy = 112900 and lPrintQueue = 14708400 and nTerm = 20
3) Run the code from Dinakar again. See the difference? Now his code only returns 1 record!

Why?

Because MAX(lPrintQueue) is still 14708400 but MAX(nTerm) is 20!! From another record.
When you tie these together against original table, there are no matching records.

My sugggestion is equal fast and works for all test data you add, change or delete.

E 12°55'05.25"
N 56°04'39.16"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-29 : 13:36:05

Good catch Peter.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

webforyou
Starting Member

15 Posts

Posted - 2007-08-30 : 13:37:22

Your helpful description makes me clear.
Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources