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-l