top N with respect to day and other field

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. Received on Thu Aug 09 2007 - 13:54:31 CDT