Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: QUERY (top 10)
Hi Seema,
> What would be query for that kind of output?
select send_date, report_sender_domain_id, spam,rn rank from (
select send_date, report_sender_domain_id, spam,
row_number() over (partition by send_date order by spam desc) as rn
from (
select trunc(time1) send_date, report_sender_domain_id, sum(spam) spam
from spam
where spam is not null
group by trunc(time1), report_sender_domain_id
)
)
where rn <= 10
order by send_date, rn;
if you want exactly 10 rows per domain and date I'd prefer to use row_number. You may also use rank (as proposed by rjamya) or dense_rank, but there are special rules to handle ties, i.e. if 11 domains produce exactly same spam per day, all 11 could appear in report using rank. Using row_number you may cut exactly 10 rows saying e.g. the domains with the same spam count are sorted alphabetically:
row_number() over (partition by send_date order by spam desc, report_sender_domain_id) as rn
HTH
Jaromir
----- Original Message -----
-- http://www.freelists.org/webpage/oracle-lReceived on Mon May 16 2005 - 17:24:56 CDT
![]() |
![]() |