Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

DB2 sql top n per group

Posted on 2011-03-08
4
Medium Priority
?
2,047 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 41

Accepted Solution

by:
Sharath earned 2000 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:Dave Ford
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

610 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