Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Posts
    61

    Unanswered: Query - Top three Values per Category

    I am attempting to design a query that will count records for a particular category and sub-category. I.E.

    Cat. Sub Cat Total
    Ford Taurus 100
    Ford Mustang 125
    Ford Crown Vic 210
    Ford Pinto 012
    Chevy Caprice 230
    Chevy Corvette 123
    Chevy Neon 100
    Chevy Monte Carlo 050


    I want the query to return the top three values for each Category/Sub-Category i.e.,Ford Taurus, Ford Mustang, Ford Crown Vic, Chevy Caprice, Chevy Corvette, Chevy Neon. How can I do this??

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use a correlated subquery with TOP
    Code:
    select Cat
         , SubCat
         , Total
      from yourtable XX
     where Total in
           ( select top 3 Total
               from yourtable
              where Cat = XX.Cat
                and SubCat = XX.SubCat
           order by Total desc )
    rudy

  3. #3
    Join Date
    Oct 2002
    Posts
    61
    Rudy,
    I tried your suggestion but for some reason or another it didn't work. Maybe I was confused with the syntax. Please re-write the subquery using the following terms:

    Table Name = Inventory
    Cat = Catogory
    SubCat = SubCatogory
    Total = Total

    Thank you very much!!!!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you want the top three in each Catogory/SubCatogory, as you initially said, it's
    Code:
    select Catogory
         , SubCatogory
         , Total
      from Inventory XX
     where Total in
           ( select top 3 Total
               from Inventory
              where Catogory = XX.Catogory
                and SubCatogory = XX.SubCatogory
           order by Total desc )
    however, if you want the top three in each Catogory, which is not what you said but what your example data suggests, then remove the condition and SubCatogory = XX.SubCatogory

    rudy

  5. #5
    Join Date
    Oct 2002
    Posts
    61

    Thumbs up

    Thanks Rudy. I was a little confused regarding the XX after the table name but that is where I messed up. Thanks again, you sample worked great!!!

Posting Permissions

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