[dba-SQLServer] Top two of each group

I did this with a cursor in the end... :( I'm sure there IS a way to do 
this otherwise, but I haven't invested more time into it, plus this 
makes it usable it returns the recordset in under 3 seconds so it seems 
fine, tho I would prefer a NON-CURSOR approach. :\ oh well.

Here is the syntax that I used..


(WATCH FOR WRAP)
-- Purpose: Select only the TOP 2 of each group Model for display
--
--

DECLARE @varModel as VarChar(100)

DECLARE curModels CURSOR FOR
SELECT  dbo.Models.Models AS Model
FROM         dbo.Frames INNER JOIN
                      dbo.Models ON dbo.Frames.Model = 
dbo.Models.modelsID INNER JOIN
                      dbo.Holding ON dbo.Frames.[S/N] = dbo.Holding.SN
WHERE     (dbo.Frames.[Start Date] > GETDATE() - 60)
        AND (dbo.Frames.Dealer = N'stock')
        AND (dbo.Models.Frames <> N'Machine Accessories')
        AND (dbo.Holding.[Hold For] IS NULL)
        AND (dbo.Models.Models <> N'TL-3')
        AND (dbo.Models.Models <> N'MDC')
        AND (dbo.Models.Models <> N'EC-300')
                AND (NOT (dbo.Models.Models LIKE N'%CE'))
        AND (NOT (dbo.Models.Models LIKE N'%HE'))
Group BY dbo.Models.models

CREATE TABLE #tmpTable
    (Model VARCHAR(100),
     Frame VARCHAR(100),
     [Est. Completion] VARCHAR (100),
     [Cast/Ctrl Ops] VARCHAR(255)
    )



OPEN curModels
FETCH NEXT FROM curModels
INTO @varModel

WHILE (@@Fetch_Status = 0)
    BEGIN
        INSERT INTO #tmpTable (Model, Frame, [Est. Completion], 
[Cast/Ctrl Ops])
        SELECT  TOP 2  dbo.Models.Models AS Model, dbo.Models.Frames AS 
Frame, dbo.prodDate(dbo.Frames.[Start Date] + 3, GETDATE())
                          AS [Est. Completion], 
ISNULL(dbo.Frames.[Cast/Ctrl Ops], '') AS [Cast/Ctrl Ops]
        FROM         dbo.Frames INNER JOIN
                          dbo.Models ON dbo.Frames.Model = 
dbo.Models.modelsID INNER JOIN
                          dbo.Holding ON dbo.Frames.[S/N] = dbo.Holding.SN
        WHERE     (dbo.Frames.[Start Date] > GETDATE() - 60) AND 
(dbo.Frames.Dealer = N'stock') AND (dbo.Models.Frames <> N'Machine 
Accessories') AND
                          (dbo.Holding.[Hold For] IS NULL) AND 
(dbo.Models.Models <> N'TL-3') AND (dbo.Models.Models <> N'MDC') AND 
(dbo.Models.Models <> N'EC-300')
                          AND (NOT (dbo.Models.Models LIKE N'%CE')) AND 
(NOT (dbo.Models.Models LIKE N'%HE'))
               AND dbo.Models.Models = @varModel
        ORDER BY dbo.Models.Models, dbo.Frames.[Start Date]
        FETCH NEXT FROM curModels
        INTO @varModel
    END
   


CLOSE curModels
DEALLOCATE curModels


SELECT * FROM #tmpTable
ORDER BY #tmpTable.Model



Drop TABLE #tmpTable


Billy Pang wrote On 5/26/2004 2:22 PM:

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


-- 
-Francisco