Re: top N with respect to day and other field

Nikolas Tautenhahn wrote:
> Hi there,
>
> I have a little problem building a query which selects top-N tuples with
> respect to day and an other field.
>
> Tha base table contains aggregated data for a web server, there are
> different directories with pages inside and hits and this is aggregated
> for a whole day for each directory (which is represented by a foreign
> key) and for each page (also a fk).
> This base table might, for example, look like
> c_date lup_page lup_dir num_hits
> 08.08.07 10 200 318
> 08.08.07 20 200 713
> 08.08.07 30 200 324
> 08.08.07 40 300 546
> 08.08.07 50 300 834
> 08.08.07 70 400 345
> 07.08.07 10 200 297
> 07.08.07 20 200 246
> 07.08.07 40 300 315
> 07.08.07 50 300 634
> 07.08.07 60 300 324
> 07.08.07 70 400 344
>
> i want to select from this table:
> the top-N pages for each directory in regard of hits for each day
>
> so, for example if we want the top-2 pages it should look like:
>
> 08.08.07 30 200 324
> 08.08.07 20 200 713
> 08.08.07 50 300 834
> 08.08.07 40 300 546
> 08.08.07 70 400 345
> 07.08.07 10 200 297
> 07.08.07 20 200 246
> 07.08.07 50 300 634
> 07.08.07 60 300 324
> 07.08.07 70 400 344
>
>
>
> the difficulties I had so far: For a fixed directory and a fixed date
> you would write
> select * from (select * from log where lup_dir=X and c_date=Y order by
> num_hits desc) where rownum <= 2
>
> But I have serious problems with modeling this in one closed statement
> for all lup_dirs and all dates...
>
> Oracle Version is 10.2.0.1.0
>
> Thanks for all help...
>
> Regards,
> N.

Consider using GROUP BY.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org