?
Solved

DB2 sql top n per group

Posted on 2011-03-08
4
Medium Priority
?
1,959 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

762 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