[dba-SQLServer] Top two of each group

Not really... if you look at it more closely, you can have more groups 
without needing to modify the code.  This is because there GROUP BY clause 
that will get the distinct values of the groups from your list and the 
subqueries will get you your top 1 and top 2 values for each group 
respectively.


>From: "Arthur Fuller" <artful at rogers.com>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: <dba-sqlserver at databaseadvisors.com>
>Subject: RE: [dba-SQLServer] Top two of each group
>Date: Fri, 28 May 2004 18:27:07 -0400
>
>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
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>

_________________________________________________________________
MSN Premium with Virus Guard and Firewall* from McAfee® Security : 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