Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 763
  • Last Modified:

Limit a query to 10 rows per column value

Hello,

I have a query:
select id, type_id, sum(values)
from table
group by id, type_id
order by 2, 3 desc;

Open in new window


The problem is that I only want the top 20 id's ( top 20 based on the sum(values) column ) for each type_id, instead of ALL ids for each type_id.

There is an ever growing amount of type_ids (i.e. 500+), so setting up a bunch of union-ed queries isn't a desirable option.

Is there a way to do this in oracle(or postgres).  Any help if greatly appreciated.

Thank You.
0
Alex Matzinger
Asked:
Alex Matzinger
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
In Oracle, try row_number().

something like this (untested.   just typed in):

select id, type_id, mySum
from (
select id, type_id, mySum, row_number() over(partition by type_id order by mySum desc) rn
from (
select id, type_id, sum(values) mySum
from table
group by id, type_id
)
)
where rn <= 20
0
 
slightwv (䄆 Netminder) Commented:
Just noticed:  Subject says top 10, question says top 20.

Whichever it is, just change "where rn <= 20" to the appropriate number.
0
 
magarityCommented:
You can use a sys_refcursor to step through the list in chunks if you'd like the first 10 or 20 and then the next batch, etc. See this Ask Tom article and the first answer from Tom:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2829457400346889644
0
 
Alex MatzingerDatabase AdministratorAuthor Commented:
This worked perfectly for me.  Thank You for your assistance
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now