[dba-SQLServer] Top two of each group

It seems to me that your solution only works if you know the groups
beforehand. The problem remains, what if you don't?

I'm working on a solution so I may reply to my own reply :)

Arthur

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Billy
Pang
Sent: Wednesday, May 26, 2004 5:23 PM
To: dba-sqlserver at databaseadvisors.com
Cc: my.lists at verizon.net
Subject: RE: [dba-SQLServer] Top two of each group


How about...

SET NOCOUNT ON
DECLARE @ttt TABLE(THE_ID INT NOT NULL IDENTITY(1,1), THE_GROUP CHAR(1),

THE_VALUE INT);

INSERT INTO @ttt VALUES('A',1);
INSERT INTO @ttt VALUES('A',3);
INSERT INTO @ttt VALUES('A',5);

INSERT INTO @ttt VALUES('B',2);
INSERT INTO @ttt VALUES('B',4);
INSERT INTO @ttt VALUES('B',5);

SELECT X.THE_GROUP
, (SELECT TOP 1 B.THE_VALUE FROM @ttt B WHERE B.THE_GROUP = X.THE_GROUP 
ORDER BY B.THE_VALUE) AS THE_FIRST_VALUE_FOR_THIS_GROUP
, (SELECT TOP 1 C.THE_VALUE FROM @ttt C WHERE C.THE_GROUP = X.THE_GROUP
AND 
C.THE_ID NOT IN (SELECT TOP 1 D.THE_ID FROM @ttt D WHERE D.THE_GROUP = 
X.THE_GROUP ORDER BY D.THE_VALUE) ORDER BY C.THE_VALUE) AS 
THE_SECOND_VALUE_FOR_THIS_GROUP
FROM @ttt X
GROUP BY THE_GROUP;


Billy

>From: Francisco H Tapia <my.lists at verizon.net>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: dba-sqlserver at databaseadvisors.com
>Subject: [dba-SQLServer] Top two of each group
>Date: Tue, 25 May 2004 12:44:15 -0700
>
>I have a little select statement, which gives me a grouping of
>
>Model1 Frame and Week1
>Model1 Frame and week2
>Model1 Frame and Week3
>Model2 Frame and Week1
>Model2 Frame and week2
>Model2 Frame and Week3
>Model2 Frame and Week3
>Model3 Frame and week2
>Model3 Frame and Week3
>... etc
>
>I'm blanking out on collecting the TOP 2 of each model, instead of 
>seeing
>the entire model list, How would one just collect the TOP two of each
model 
>type?
>
>I can think of how to do this in a cursor, but I'd like to avoid that 
>if at
>all possible.
>
>--
>-Francisco
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>

_________________________________________________________________
MSN Premium includes powerful parental controls and get 2 months FREE*

http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU
=http://hotmail.com/enca&HL=Market_MSNIS_Taglines

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com