Re: rownum HELP ....

From: Darta <tvesel_at_hotmail.com>
Date: 4 Aug 2003 09:05:32 -0700
Message-ID: <dbc964ae.0308040805.2b77755a_at_posting.google.com>

Thanks! This is exactly what I was looking for.

Another solution I found is this one (my friends are awesome!):

SELECT tmp.version_id, rpt.*, version.*
FROM (select max(version.version_no) - rpt.max_report as start_max_version,

      rpt.report_id
      from version, report rpt
      where rpt.report_id = version.report_id
      group by rpt.report_id,rpt.max_report) tmp, report rpt, version

WHERE tmp.report_id = rpt.report_id
AND version.report_id = rpt.report_id
AND version.version_no > tmp.start_max_version ORDER BY rpt.sort_order, rpt.report_id, version.version_no desc

This one also works since we have version numbers never going out of sync - meaning if I have 25 versions (all our versions have sequential numbers starting from 1), we would never delete version number 23. Your solution is excellent and it is more foolproof that the one above ... THANKS A LOT!!!! Darta

bung_ho_at_hotmail.com (bung ho) wrote in message news:<567a1b1.0307301208.7888b4c2_at_posting.google.com>...
> tvesel_at_hotmail.com (Darta) wrote in message news:<dbc964ae.0307290801.3e1de9ef_at_posting.google.com>...
> > Maybe I shouldn't have posted the SQL since I did it fast and there
> > are some stuff that are obviously wrong.... But we need to get passed
> > the synthax point...
> >
> > So, let me try again (please disregard my wrong SQL):
> >
> > Based on the data model I presented below, if the user passed as an
> > input the division name ("DIVISION.division_nm"), I need to return all
> > the VERSIONs per EACH REPORT in that DIVISION. Only the latest
> > REPORT.max_display number of VERSIONs have to be returned per EACH
> > report in that DIVISION. So if the DIVISION has 5 reports, and each
> > report has N number of version in VERSION table, per each
> > REPORT.max_display for that report, I need to only return
> > REPORT.max_display number of versions per each report.
> >
> > It sounds easy, but I just can't put my finger at it. The easy part
> > would be to get all the versions per each report - you can join all
> > the way through from DIVISION to VERSION. But the hard part is - how
> > to limit number of VERSION rows returned based on REPORT.max_display
> > field per each report for those versions?
> >
> > Can you/anyone help...?
> >
> > Darta
> >
>
> you don't say what version of oracle you are using: you need a version
> that supports analytic functions for this solution to work.
>
> also, it would help if you posted the actual table structures, sample
> data, and desired results, but given what you say i'm guessing you
> have something like the following:
>
> SQL> select * from report;
>
> REPORT_ID MAX_REPORT
> ---------- ----------
> 1 2
> 2 3
> 3 4
>
> meaning that report 1 should show the top 2 versions, report 2 should
> show the top 3 versions, and report 3 should show the top 4 versions.
> and then,
>
> SQL> select * from version;
>
> VERSION_ID REPORT_ID
> ---------- ----------
> 10 1
> 20 1
> 30 1
> 40 1
> 50 1
> 100 2
> 200 2
> 300 2
> 400 2
> 500 2
> 1000 3
> 2000 3
> 3000 3
> 4000 3
> 5000 3
>
> 15 rows selected.
>
> so i've given 5 versions to each of the 3 existing reports. if i've
> understood correctly, the following query should get you what you
> need:
>
> 1 select tmp.version_id, rpt.report_id from
> 2 (select version_id,
> 3 report_id,
> 4 row_number() over (partition by report_id
> 5 order by version_id desc) v_no
> 6 from version) tmp, report rpt
> 7 where rpt.report_id = tmp.report_id and
> 8* tmp.v_no <= rpt.max_report
> SQL> /
>
> VERSION_ID REPORT_ID
> ---------- ----------
> 50 1
> 40 1
> 500 2
> 400 2
> 300 2
> 5000 3
> 4000 3
> 3000 3
> 2000 3
>
>
> so for report 1, we got the top 2 versions, report 2 got the top 3,
> report 3 got the top 4. this uses the analytic function row_number(),
> which is sort of like rownum but it can "reset" itself according to
> the value of other columns.
>
> i left out the division and division_report stuff, but of course all
> you need to do it join those with the above resultset.
Received on Mon Aug 04 2003 - 18:05:32 CEST