Solved

DB2 sql top n per group

Posted on 2011-03-08
4
1,757 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

813 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

13 Experts available now in Live!

Get 1:1 Help Now