Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2007
    Posts
    21

    Unanswered: Problem with Unions

    Hello All,
    I have a small problem with union's and rownum in oracle 9i.

    What I ultimatly want to do is to have a query like this: (note this is just an example)

    Select name, id from (

    select name, id from customer
    union all select name, id from employee

    ) where rownum > 5 and rownum < 10.

    So basically pagination of results.

    But when I run this query I get no results because of the fact that there are no results where rownum > 5 and rownum < 10 (it only gives me results when I have rownum >= 1)

    So I thought I could put the rownum in the union like so:

    Select name, id, rn from (

    select name, id, rownum from customer
    union all select name, id, rownum as rn from employee

    ) where rn > 5 and rn < 10.

    But this wont work because the rownum in each union is only local to that specific table, so the results of the union is like this:

    customerA, 777, 1
    customerB, 778, 2
    customerC, 779, 3
    employeeG, 123, 1
    employeeH, 124, 2
    employeeI, 125, 3

    NOT

    customerA, 777, 1
    customerB, 778, 2
    customerC, 779, 3
    employeeG, 123, 4
    employeeH, 124, 5
    employeeI, 125, 6

    How can I fix this?

    Thanks heaps!!

  2. #2
    Join Date
    Jun 2007
    Posts
    21
    As it turns out, an approach that I thought would not be very efficient was recommended by "Tom" on this site:

    http://www.oracle.com/technology/ora...o56asktom.html

    see the "Pagination with ROWNUM" section.

    Thats how I did it and it works (so far)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •