Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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