Solved

DB2 sql top n per group

Posted on 2011-03-08
4
1,736 Views
Last Modified: 2012-06-22
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.
0
Comment
Question by:se_1581
4 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 35074966
try

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
0
 

Author Comment

by:se_1581
ID: 35075410
it's not returning the top 3 customer sales, it returns alphabetical first 3 rows, although i have a sort descending by sales
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35075440
post some sample data with expected result. Or use sales column in ORDER BY clause.
select * from
  (select *, row_number() over( partition by item_id order by sales desc) rown
   from your_table
)
where rown <= 3

Open in new window

0
 
LVL 18

Expert Comment

by:daveslash
ID: 35075651
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

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now