se_1581
asked on
DB2 sql top n per group
Hi,
Does anyone know the syntax to get top n rows per group in DB2? for example top 3 customer sales for a set of items.
Thanks.
Does anyone know the syntax to get top n rows per group in DB2? for example top 3 customer sales for a set of items.
Thanks.
ASKER
it's not returning the top 3 customer sales, it returns alphabetical first 3 rows, although i have a sort descending by sales
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That's really cool, Momi. You are the MAN.
select *
from deleteme
CATEGORY THEVALUE
a 1
a 2
a 3
a 4
b 4
b 5
b 6
b 7
b 8
c 9
c 10
c 11
c 12
c 13
with temptable as (
select category,
thevalue,
row_number() over( partition by category
order by theValue desc ) as rown
from deleteme
)
select category,
theValue
from temptable
where rown <=2
CATEGORY THEVALUE
a 4
a 3
b 8
b 7
c 13
c 12
select * from
(select *, row_number() over( partition by item_id order by customer_id ) rown
from your_table
)
where rown <= 3
you change the order by to get the exact customers you want