TOP 3 Kinda?

  • My table has a field called category it's a foreign key.

    I need to create a query that returns the top three records from each category. The number of categories is unknown.

    Desired results

    Category    Field1    Field2       Field3

    1               blah      blah         blah

    1               blah      blah         blah

    1               blah      blah         blah

    2              blah      blah         blah

    2               blah      blah         blah

    2               blah      blah         blah

    3              blah      blah         blah

    3              blah      blah         blah

    3              blah      blah         blah

     

    Any help is appreciated!  Thanks,

    Paully

  • Maybe this can get you started.  I'm sure someone else can come up with a more elegant solution.

    IF EXISTS (SELECT name

        FROM   sysobjects

        WHERE  name = N'tblCategory'

        AND    type = 'U')

        DROP TABLE tblCategory

    GO

    Create table tblCategory

    (

    id int,

    field1 varchar(20),

    field2 varchar(20)

    )

    Go

    Insert into tblCategory

    Select 1, '1aaa', '1aaa'

    Union All

    Select 1, '1bbb', '1bbb'

    Union All

    Select 1, '1ccc', '1ccc'

    Union All

    Select 1 , '1ddd', '1ddd'

    Union All

    Select 2, '2aaa', '2aaa'

    Union All

    Select 2, '2bbb', '2bbb'

    Union All

    Select 2, '2ccc', '2ccc'

    Union All

    Select 2 , '2ddd', '2ddd'

    ----------------------------------

    ----------------------------------

    Select c1.id, c1.field1, c1.field2

    From tblCategory c1

    Where c1.field1 IN (Select top 3 field1

                               From tblCategory c2

                              Where c1.id = c2.id

                               Order By c2.id, field1, field2)

    And c1.field2 IN (Select top 3 field2

                           From tblCategory c2

                           Where c1.id = c2.id

                           Order By c2.id, field1, field2)

    Order by c1.id, c1.field1, c1.field2

    -----------------------------------

    drop table tblCategory

    -----------------------------------

    ID Field1   Field2

    ----------------

    1  1aaa     1aaa

    1  1bbb     1bbb

    1  1ccc     1ccc

    2  2aaa     2aaa

    2  2bbb     2bbb

    2  2ccc     2ccc

     

  • This is by no means elegant but since the # of categories is UNKNOWN I cannot think you can avoid cursors.....something like this in a stored procedure/UDF ?!?!

    CREATE PROCEDURE Get_Top3

    AS

    DECLARE @CategoryID Int

    CREATE TABLE #TempCategory

    (

    Category Int,

    Field1 VarChar(50),

    Field2 VarChar(50),

    Field3 VarChar(50)

    )

    BEGIN

    DECLARE ScrollCategory CURSOR

    FOR

    SELECT DISTINCT Category FROM tblCategory

    OPEN ScrollCategory

    FETCH ScrollCategory INTO @CategoryID

    WHILE @@FETCH_STATUS = 0--while there are rows to be fetched

    BEGIN

    INSERT INTO #TempCategory

    (SELECT TOP 3 * FROM tblCategory

    WHERE Category = @CategoryID

    ORDER BY ???)

    FETCH ScrollCategory INTO @CategoryID

    END

    END

    CLOSE ScrollCategory

    DEALLOCATE ScrollCategory

    --now get all your rows from temp table

    SELECT * FROM #TempCategory ORDER BY CategoryID







    **ASCII stupid question, get a stupid ANSI !!!**

  • Thanks...I was hoping for one of those elegant non-cursor solutions, but I was thinking last night too, this might NEED a cursor.

     

    BTW, that code was spot on...I was pretty much able to use it exactly as written...THANK YOU sushila!

  • Great! Thanks for feedback!







    **ASCII stupid question, get a stupid ANSI !!!**

  • I'm sure you can come up with a solution that doesn't use cursors.

    If you make a UDF that looks to see if a row is in the top 3 for that category, something like:

    create function dbo.uf_istop3(@rowid int, @cat int) returns int as

    begin

    if @rowid in (select top 3 rowid from tblCategory c where c.categoryid = @cat order by ???)

    return (1)

    else

    return (0)

    end

    Then you can do something like:

    select * from tblCategory

    where dbo.uf_istop3(rowid, categoryid) = 1

    and have it return what you're after...

    PS: I'm just using 'rowid', because I'm not sure of the best way of referring to your rows. You should have some sort of ID field in your table.

    Does this do what you're after?

    Rob

    Rob Farley
    LobsterPot Solutions & Adelaide SQL Server User Group
    Company: http://www.lobsterpot.com.au
    Blog: http://blogs.lobsterpot.com.au

  • Sorry - small typo. The 'else' statement in there makes the function invalid. If you remove it, then it's fine. Better still, surround 'return(1)' in 'begin' and 'end'.

    Here's an example I tried using the Northwind database. It works well, looking for each employees top 3 orders.

    create function dbo.uf_istop3(@orderid int, @employeeid int) returns int as

    begin

    if @orderid in (

    select top 3 o.orderid

    from orders o

    join

    [Order Details] od

    on od.orderid = o.orderid

    where o.employeeid = @employeeid

    group by o.orderid

    order by sum(od.unitprice * od.quantity * (1.0-od.discount))

    )

    begin

    return (1)

    end

    return (0)

    end

    go

    grant execute on dbo.uf_istop3 to public

    go

    select *

    from employees e

    join

    orders o

    on o.employeeid = e.employeeid

    where dbo.uf_istop3(o.orderid, e.employeeid) = 1

    order by e.lastname

    Rob Farley
    LobsterPot Solutions & Adelaide SQL Server User Group
    Company: http://www.lobsterpot.com.au
    Blog: http://blogs.lobsterpot.com.au

  • If your table has a single-column primary key (column pk in the following SQL), then it should be simple:

    /*fictitious table for testing*/

    CREATE TABLE tbl (pk int identity primary key, category int, field1 int, field2 int)

    INSERT INTO tbl (category, field1,field2) VALUES (1,1,1)

    INSERT INTO tbl (category, field1,field2) VALUES (1,2,3)

    INSERT INTO tbl (category, field1,field2) VALUES (1,5,4)

    INSERT INTO tbl (category, field1,field2) VALUES (1,4,6)

    INSERT INTO tbl (category, field1,field2) VALUES (1,8,1)

    INSERT INTO tbl (category, field1,field2) VALUES (2,2,2)

    INSERT INTO tbl (category, field1,field2) VALUES (2,1,1)

    INSERT INTO tbl (category, field1,field2) VALUES (2,1,4)

    INSERT INTO tbl (category, field1,field2) VALUES (2,7,1)

    INSERT INTO tbl (category, field1,field2) VALUES (2,3,9)

    INSERT INTO tbl (category, field1,field2) VALUES (3,1,8)

    INSERT INTO tbl (category, field1,field2) VALUES (3,3,1)

    INSERT INTO tbl (category, field1,field2) VALUES (3,2,4)

    INSERT INTO tbl (category, field1,field2) VALUES (3,1,7)

    INSERT INTO tbl (category, field1,field2) VALUES (3,5,1)

    INSERT INTO tbl (category, field1,field2) VALUES (3,1,1)

    /*for each category, find the top 3 entries based on field2*/

    SELECT category, field1, field2

    FROM tbl t

    WHERE t.pk IN (SELECT TOP 3 pk from tbl WHERE tbl.category = t.category ORDER BY field2 desc)

    ORDER BY category, field2 desc

    Just another occasion where identity column will help; if you don't have it, it will be more complicated, but it still should be possible.

  • See if this helps:

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=180032#bm180450

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Paully - Vladan's t-sql is your "elegant" solution....







    **ASCII stupid question, get a stupid ANSI !!!**

  • Totally elegant...Thanks it worked great.  Sushila--Thanks too, while I was waiting for the elegant solution I was still able to test the page.

     

    Paully21

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply