Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008
    Posts
    14

    Question Unanswered: Duplicate Data when using Order by and ROWNUM

    Hi,
    Iam trying to fetch some data from the database and sorting it using Order by clause.
    Iam using the ROWNUM clause to restrict the number of records being fetched.
    Sample code given below

    /************************************************/
    SELECT *
    FROM (SELECT a.*,
    ROWNUM rnum
    FROM (
    ((SELECT /*+ FIRST_ROWS */ DISTINCT c1,c2,c3
    FROM Table 1
    WHERE conditions
    UNION ALL
    SELECT /*+ FIRST_ROWS */ DISTINCT c1,c2,c3
    FROM Table 2
    WHERE conditions
    )
    ORDER BY document_type) a
    WHERE ROWNUM <= To_number(max_rows + start_seq))
    WHERE rnum > To_number(start_seq)
    /************************************************** ***/
    If the start_seq is given as 0 and max_rows(no of rows that should be returned) as 25 , it returns 25 rows.
    However if I give start_seq as 25 and max_rows as 25, it should ideally return next 25 rows. But instead , it returns few rows which were already fetched in the first condition.

    However if i run the query with start_seq as 0 and max_rows as 50,there are no duplicates.

    The issue happens because there are duplicate 'document_type' using which, we are ordering the data. If i replace it with a unique column, the issue is resolved.

    But I would like to know how this is happening. ie is this something to do with the Order by clause or an issue when we are suing Row Num.

    Hope my query is clear.
    Please let me know if you need any further details

    Thanks and Regards
    Vipin

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Since your ORDER BY clause does not completely determine the order of rows returned by the query (it puts all the rows with the same document_type together, but they can be in any order within document_type), there is no guarantee that the second run gets it results in exactly the same order as the first. In fact, I got these results using the EMP table (and ignoring the final WHERE clause to see what is happening):
    Code:
    SQL> exec :start_seq := 0
    SQL> exec :max_rows := 5
    SQL> SELECT *
      2  FROM
      3  ( SELECT a.*,
      4           ROWNUM rnum
      5    FROM
      6    (
      7      ( SELECT empno, ename, deptno
      8        FROM emp
      9      )
     10      ORDER BY deptno
     11    ) a
     12    WHERE ROWNUM <= :max_rows + :start_seq
     13  );
    
         EMPNO ENAME          DEPTNO       RNUM
    ---------- ---------- ---------- ----------
          7934 MILLER             10          1
          7777 Roger              10          2
          1144 Carter             10          3
          5001 TEST               10          4
          7782 CLARK              10          5
    
    exec :start_seq := 5;
    SQL> /
    
         EMPNO ENAME          DEPTNO       RNUM
    ---------- ---------- ---------- ----------
          7934 MILLER             10          1
          7777 Roger              10          2
          1144 Carter             10          3
          7839 KING               10          4
          5001 TEST               10          5
          7782 CLARK              10          6
          1212 TEST               10          7
          1111 DDDD               10          8
             1                    10          9
          8001 FARMER             10         10
    As you can see, CLARK moved from row 5 to row 6!

    Solution: Add something unique after document_type in the ORDER BY clause - e.g. "ORDER BY document_type, id"

  3. #3
    Join Date
    Mar 2008
    Posts
    14
    Thanks a lot Andrew

Posting Permissions

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