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

Try this

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

Try this

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

where rownum < 11

will return then top 10 records

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 ?