Re: rownum HELP ....

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

        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 Wed Jul 30 2003 - 22:08:54 CEST