Professional Web Applications Themes

Top N within Group - Microsoft SQL / MS SQL Server

Hello, I am trying to get the TOP 2 highest stock products for a department. I tried using a combination of Top N and Group by clause and subqueries, but nothing seems to work. There will be hundreds of departments and over the million of products, so performance is very crucial. I was thinking about using the cursor for department and run the top n query for each department, but that would be way too ugly for something like this. Any help is appreciated. Thanks. declare Departments table( departmentID numeric(9) ) insert into departments Values(1000) insert into departments Values(1001) declare ...

  1. #1

    Default Top N within Group

    Hello,

    I am trying to get the TOP 2 highest stock products for a department. I
    tried using a combination of Top N and Group by clause and subqueries, but
    nothing seems to work. There will be hundreds of departments and over the
    million of products, so performance is very crucial. I was thinking about
    using the cursor for department and run the top n query for each department,
    but that would be way too ugly for something like this. Any help is
    appreciated. Thanks.

    declare Departments table(
    departmentID numeric(9)
    )

    insert into departments Values(1000)
    insert into departments Values(1001)

    declare Products table (
    productID numeric(9),
    departmentID numeric(9),
    qty int
    )

    insert into products Values (5656, 1000, 10)
    insert into products Values (5657, 1001, 6)
    insert into products Values (5658, 1000, 5)
    insert into products Values (5659, 1000, 3)
    insert into products Values (5876, 1001, 1)
    insert into products Values (2222, 1001, 0)
    insert into products Values (2223, 1000, 1)

    ----------------------------------------------------
    Desired Output
    1000 5656
    1000 5658

    1001 5657
    1001 5876


    Gedas Guest

  2. #2

    Default Re: Top N within Group

    Try:

    select
    o.departmentID
    , o.productID
    from
    products o
    where
    o.productid in
    (
    select top 2
    i.productid
    from
    products i
    where
    i.departmentID = o.departmentId
    order by
    i.qty desc
    )
    order by
    o.departmentID
    , o.qty desc

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Gedas Gudenas" <com> wrote in message news:#phx.gbl...
    Hello,

    I am trying to get the TOP 2 highest stock products for a department. I
    tried using a combination of Top N and Group by clause and subqueries, but
    nothing seems to work. There will be hundreds of departments and over the
    million of products, so performance is very crucial. I was thinking about
    using the cursor for department and run the top n query for each department,
    but that would be way too ugly for something like this. Any help is
    appreciated. Thanks.

    declare Departments table(
    departmentID numeric(9)
    )

    insert into departments Values(1000)
    insert into departments Values(1001)

    declare Products table (
    productID numeric(9),
    departmentID numeric(9),
    qty int
    )

    insert into products Values (5656, 1000, 10)
    insert into products Values (5657, 1001, 6)
    insert into products Values (5658, 1000, 5)
    insert into products Values (5659, 1000, 3)
    insert into products Values (5876, 1001, 1)
    insert into products Values (2222, 1001, 0)
    insert into products Values (2223, 1000, 1)

    ----------------------------------------------------
    Desired Output
    1000 5656
    1000 5658

    1001 5657
    1001 5876



    Tom Guest

  3. #3

    Default Re: Top N within Group

    Here's a much simpler query

    select a.departmentid,a.productid
    from products a
    where qty in ( select top 2 qty from products
    where departmentid=a.departmentid
    order by qty desc )
    order by a.departmentid

    "Tom Moreau" <spam.me.cips.ca> wrote in message news:%23F%phx.gbl...
    Try:

    select
    o.departmentID
    , o.productID
    from
    products o
    where
    o.productid in
    (
    select top 2
    i.productid
    from
    products i
    where
    i.departmentID = o.departmentId
    order by
    i.qty desc
    )
    order by
    o.departmentID
    , o.qty desc

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Gedas Gudenas" <com> wrote in message news:#phx.gbl...
    Hello,

    I am trying to get the TOP 2 highest stock products for a department. I
    tried using a combination of Top N and Group by clause and subqueries, but
    nothing seems to work. There will be hundreds of departments and over the
    million of products, so performance is very crucial. I was thinking about
    using the cursor for department and run the top n query for each department,
    but that would be way too ugly for something like this. Any help is
    appreciated. Thanks.

    declare Departments table(
    departmentID numeric(9)
    )

    insert into departments Values(1000)
    insert into departments Values(1001)

    declare Products table (
    productID numeric(9),
    departmentID numeric(9),
    qty int
    )

    insert into products Values (5656, 1000, 10)
    insert into products Values (5657, 1001, 6)
    insert into products Values (5658, 1000, 5)
    insert into products Values (5659, 1000, 3)
    insert into products Values (5876, 1001, 1)
    insert into products Values (2222, 1001, 0)
    insert into products Values (2223, 1000, 1)

    ----------------------------------------------------
    Desired Output
    1000 5656
    1000 5658

    1001 5657
    1001 5876


    Partha Guest

  4. #4

    Default Re: Top N within Group

    I don't know why you consider that easier;
    however, I do know you should consider
    taking a second look the results produced
    by your query after adding some more data
    to the script:

    insert into products Values (9001, 1000, 10)
    insert into products Values (9002, 1000, 10)
    insert into products Values (9003, 1000, 10)

    Bye,
    Delbert Glass

    "Partha Mandayam" <com> wrote in message news:phx.gbl...
    Here's a much simpler query

    select a.departmentid,a.productid
    from products a
    where qty in ( select top 2 qty from products
    where departmentid=a.departmentid
    order by qty desc )
    order by a.departmentid

    "Tom Moreau" <spam.me.cips.ca> wrote in message news:%23F%phx.gbl...
    Try:

    select
    o.departmentID
    , o.productID
    from
    products o
    where
    o.productid in
    (
    select top 2
    i.productid
    from
    products i
    where
    i.departmentID = o.departmentId
    order by
    i.qty desc
    )
    order by
    o.departmentID
    , o.qty desc

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Gedas Gudenas" <com> wrote in message news:#phx.gbl...
    Hello,

    I am trying to get the TOP 2 highest stock products for a department. I
    tried using a combination of Top N and Group by clause and subqueries, but
    nothing seems to work. There will be hundreds of departments and over the
    million of products, so performance is very crucial. I was thinking about
    using the cursor for department and run the top n query for each department,
    but that would be way too ugly for something like this. Any help is
    appreciated. Thanks.

    declare Departments table(
    departmentID numeric(9)
    )

    insert into departments Values(1000)
    insert into departments Values(1001)

    declare Products table (
    productID numeric(9),
    departmentID numeric(9),
    qty int
    )

    insert into products Values (5656, 1000, 10)
    insert into products Values (5657, 1001, 6)
    insert into products Values (5658, 1000, 5)
    insert into products Values (5659, 1000, 3)
    insert into products Values (5876, 1001, 1)
    insert into products Values (2222, 1001, 0)
    insert into products Values (2223, 1000, 1)

    ----------------------------------------------------
    Desired Output
    1000 5656
    1000 5658

    1001 5657
    1001 5876


    Delbert Guest

  5. #5

    Default Re: Top N within Group

    Thanks, that will work.


    "Tom Moreau" <spam.me.cips.ca> wrote in message
    news:#F#phx.gbl...
    Try:

    select
    o.departmentID
    , o.productID
    from
    products o
    where
    o.productid in
    (
    select top 2
    i.productid
    from
    products i
    where
    i.departmentID = o.departmentId
    order by
    i.qty desc
    )
    order by
    o.departmentID
    , o.qty desc

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Gedas Gudenas" <com> wrote in message
    news:#phx.gbl...
    Hello,

    I am trying to get the TOP 2 highest stock products for a department. I
    tried using a combination of Top N and Group by clause and subqueries, but
    nothing seems to work. There will be hundreds of departments and over the
    million of products, so performance is very crucial. I was thinking about
    using the cursor for department and run the top n query for each department,
    but that would be way too ugly for something like this. Any help is
    appreciated. Thanks.

    declare Departments table(
    departmentID numeric(9)
    )

    insert into departments Values(1000)
    insert into departments Values(1001)

    declare Products table (
    productID numeric(9),
    departmentID numeric(9),
    qty int
    )

    insert into products Values (5656, 1000, 10)
    insert into products Values (5657, 1001, 6)
    insert into products Values (5658, 1000, 5)
    insert into products Values (5659, 1000, 3)
    insert into products Values (5876, 1001, 1)
    insert into products Values (2222, 1001, 0)
    insert into products Values (2223, 1000, 1)

    ----------------------------------------------------
    Desired Output
    1000 5656
    1000 5658

    1001 5657
    1001 5876




    Gedas Guest

Similar Threads

  1. "group by" - order of rows in group
    By aljosa.mohorovic@gmail.com in forum MySQL
    Replies: 1
    Last Post: September 1st, 08:50 AM
  2. New group
    By James Jones in forum ASP Components
    Replies: 0
    Last Post: July 27th, 01:44 AM
  3. May 29 Sydney Developers Group study group
    By 105 in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: May 25th, 02:56 PM
  4. cfgrid inside a <cfoutput query="myQuery" group="GROUP">
    By DavidGhous in forum Coldfusion Flash Integration
    Replies: 1
    Last Post: April 12th, 07:23 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139