[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