Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    Lisbon, Portugal
    Posts
    1

    Unanswered: Top Nth Rows of a Group By

    Hi ...

    I have a table called tblProducts (idProduct, idCategory, Price)

    I want to build a single query wich returns the Nth products with higher price for each category.

    It's probably quite simple, but I can't manage to get it ...
    Maybe it's because I'm tired tired tired ...

    Thanks a lot!!

    Bruno Lopes

  2. #2
    Join Date
    May 2003
    Posts
    45

    Re: Top Nth Rows of a Group By

    Try this

    select idproduct,max(price) from tblproducts
    group by idproduct



    Originally posted by BrunoLopes
    Hi ...

    I have a table called tblProducts (idProduct, idCategory, Price)

    I want to build a single query wich returns the Nth products with higher price for each category.

    It's probably quite simple, but I can't manage to get it ...
    Maybe it's because I'm tired tired tired ...

    Thanks a lot!!

    Bruno Lopes

  3. #3
    Join Date
    Oct 2002
    Location
    Argentina
    Posts
    72
    Try this

    select * from (
    select idproduct,max(price),rownum from tblproducts
    group by idproduct)

    where rownum < 11

    will return then top 10 records

  4. #4
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130

    Re: Top Nth Rows of a Group By

    Originally posted by BrunoLopes
    Hi ...

    I have a table called tblProducts (idProduct, idCategory, Price)

    I want to build a single query wich returns the Nth products with higher price for each category.

    It's probably quite simple, but I can't manage to get it ...
    Maybe it's because I'm tired tired tired ...

    Thanks a lot!!

    Bruno Lopes
    Do you use Oracle 8i + ? For N = 2

    SQL> create table tblProducts (idProduct int, idCategory int, price int);

    Table created.

    SQL> insert into tblProducts select rownum, mod(rownum,2), 100*rownum from all_objects where rownum <= 8;

    8 rows created.

    SQL> select * from tblProducts order by IDCATEGORY,PRICE desc;

    IDPRODUCT IDCATEGORY PRICE
    ---------- ---------- ----------
    8 0 800
    6 0 600
    4 0 400
    2 0 200
    7 1 700
    5 1 500
    3 1 300
    1 1 100

    8 rows selected.

    SQL> select IDCATEGORY, IDPRODUCT, price from (
    2 select IDCATEGORY, IDPRODUCT, price, rank() over (partition by IDCATEGORY order by price desc) r
    3 from tblproducts
    4 ) where r <= 2;

    IDCATEGORY IDPRODUCT PRICE
    ---------- ---------- ----------
    0 8 800
    0 6 600
    1 7 700
    1 5 500

    You can use dense_rank() instead of rank(), depending on what you would do if you have e.g. 2 products with price = 800, and 1 with price=700 in category 0 - do you return 2 or 3 records ?

Posting Permissions

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