Home » SQL & PL/SQL » SQL & PL/SQL » ROW_NUMBER() not working properly?
ROW_NUMBER() not working properly? [message #683543] Thu, 28 January 2021 12:37 Go to next message
chachamenci
Messages: 2
Registered: January 2021
Junior Member
Hello,

I have a query that uses ROW_NUMBER() OVER( ORDER BY.. and returns the rows in the desired order, but when I try to get just the row with ROW_NUMBER = 1 by doing an outer SELECT, I get the wrong result.
To help illustrate this, I've created a sample test table and the queries I use for you to understand.

My test table that looks like this:

create table test_rownum (text varchar2(20), num_1 number, num_2 number);

insert into test_rownum values ('AAAAZF', null, 2453193);
insert into test_rownum values ('AAAAZG', null, 2453193);

If I use the following query:

SELECT
ROW_NUMBER() OVER(
ORDER BY NVL(num_1,9999999999) DESC, NVL(num_2,9999999999) DESC
) row_num,
text, num_1, num_2
FROM test_rownum;

The result is:

1 AAAAZG 2453193
2 AAAAZF 2453193

So if I understand correctly, if I add an outer select to get just the row with row_num = 1, the row with text = AAAAZG should be selected.
However, the other row is selected

select *
from (
SELECT
ROW_NUMBER() OVER(
ORDER BY NVL(num_1,9999999999) DESC, NVL(num_2,9999999999) DESC
) row_num,
text, num_1, num_2
FROM test_rownum
) where row_num = 1 ;

1 AAAAZF 2453193

There must be something I'm missing or doing absolutely wrong!!
Thanks in advance for your help.
Re: ROW_NUMBER() not working properly? [message #683545 is a reply to message #683543] Thu, 28 January 2021 14:12 Go to previous messageGo to next message
Michel Cadot
Messages: 67817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

Quote:
the row with text = AAAAZG should be selected.
Not really, your ROW_NUMBER expression does not specify a strict order in the set, actually both
   ROW_NUM TEXT                      NUM_1      NUM_2
---------- -------------------- ---------- ----------
         1 AAAAZG                             2453193
         2 AAAAZF                             2453193
and
   ROW_NUM TEXT                      NUM_1      NUM_2
---------- -------------------- ---------- ----------
         1 AAAAZF                             2453193
         2 AAAAZG                             2453193
are equally correct results.
So Oracle chose the first one for your first query and the second one for your second query.
If you want a strict order, you have to add TEXT in the ORDER BY clause of your ROW_NUMBER.

SQL> SELECT
  2    ROW_NUMBER() OVER(
  3      ORDER BY NVL(num_1,9999999999) DESC, NVL(num_2,9999999999) DESC, text
  4    ) row_num,
  5    text, num_1, num_2
  6  FROM test_rownum;
   ROW_NUM TEXT                      NUM_1      NUM_2
---------- -------------------- ---------- ----------
         1 AAAAZF                             2453193
         2 AAAAZG                             2453193

2 rows selected.

SQL> select * from (
  2  SELECT
  3    ROW_NUMBER() OVER(
  4      ORDER BY NVL(num_1,9999999999) DESC, NVL(num_2,9999999999) DESC, text
  5    ) row_num,
  6    text, num_1, num_2
  7  FROM test_rownum
  8  ) where row_num = 1 ;
   ROW_NUM TEXT                      NUM_1      NUM_2
---------- -------------------- ---------- ----------
         1 AAAAZF                             2453193

1 row selected.

[Updated on: Thu, 28 January 2021 14:15]

Report message to a moderator

Re: ROW_NUMBER() not working properly? [message #683547 is a reply to message #683543] Fri, 29 January 2021 04:24 Go to previous messageGo to next message
John Watson
Messages: 8535
Registered: January 2010
Location: Global Village
Senior Member
If you look at the exec plan for your query, you'll see that the rownum=1 predicate is being pushed into the subquery where it gets applied to row_number() at operation 2:
orclz> select *
  2  from (
  3  SELECT
  4  ROW_NUMBER() OVER(
  5  ORDER BY NVL(num_1,9999999999) DESC, NVL(num_2,9999999999) DESC
  6  ) row_num,
  7  text, num_1, num_2
  8  FROM test_rownum
  9  ) where row_num = 1 ;

        ROW_NUM TEXT                           NUM_1           NUM_2
--------------- -------------------- --------------- ---------------
              1 AAAAZF                                       2453193


Execution Plan
----------------------------------------------------------
Plan hash value: 127541510

----------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |     1 |    51 |     4  (25)| 00:00:01 |
|*  1 |  VIEW                    |             |     1 |    51 |     4  (25)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|             |     2 |    76 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | TEST_ROWNUM |     2 |    76 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ROW_NUM"=1)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY NVL("NUM_1",9999999999) DESC
              ,NVL("NUM_2",9999999999) DESC )<=1)
I tried to prevent this with hints (no_merge, no_push_pred, materialize, no_query_rewrite) but couldn't stop it. However, this variation of your query does return the row you were expecting because the subquery is indeed materialized at operation 3:
orclz> select *
  2  from (
  3  SELECT
  4  ROW_NUMBER() OVER(
  5  ORDER BY NVL(num_1,9999999999) DESC, NVL(num_2,9999999999) DESC
  6  ) row_num,
  7  text, num_1, num_2
  8  FROM test_rownum
  9  ) fetch first 1 rows only;

        ROW_NUM TEXT                           NUM_1           NUM_2
--------------- -------------------- --------------- ---------------
              1 AAAAZG                                       2453193


Execution Plan
----------------------------------------------------------
Plan hash value: 3591935234

--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |     1 |    64 |     4  (25)| 00:00:01 |
|*  1 |  VIEW                  |             |     1 |    64 |     4  (25)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY|             |     2 |   102 |     4  (25)| 00:00:01 |
|   3 |    VIEW                |             |     2 |   102 |     4  (25)| 00:00:01 |
|   4 |     WINDOW SORT        |             |     2 |    76 |     4  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL | TEST_ROWNUM |     2 |    76 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=1)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY  NULL )<=1)
However, the result is still (as MC pointed out) non-deterministic. I don't know why my use of hints did not achieve the same effect.
Re: ROW_NUMBER() not working properly? [message #683552 is a reply to message #683543] Fri, 29 January 2021 13:22 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3047
Registered: January 2010
Location: Connecticut, USA
Senior Member
chachamenci wrote on Thu, 28 January 2021 13:37
Hello,

I have a query that uses ROW_NUMBER() OVER( ORDER BY.. and returns the rows in the desired order, but when I try to get just the row with ROW_NUMBER = 1 by doing an outer SELECT, I get the wrong result.
No, your expectations are wrong. There is no row order in relational tables unless ORDER BY is used. But even then rows can be returned in a different order next time query it run if ORDER BY is non-deterministic and there are multiple rows for ORDER BY expression values. For example:

SELECT  *
  FROM  EMP
  ORDER BY DEPTNO
/
All we are guaranteed is department 10 employees will apprear first, followed by department 20 employees, followed by department 30 employees. But we are not guaranteed employees within same department will be displayed in same order each time we run the above query. Now back to your query. It calculates ROW_NUMBER based on NVL(num_1,9999999999) DESC, NVL(num_2,9999999999) DESC. Now look values NVL produces:

SELECT  TEXT,
        NVL(num_1,9999999999) C1,
        NVL(num_2,9999999999) C2
  FROM  test_rownum
/

TEXT                         C1         C2
-------------------- ---------- ----------
AAAAZF               9999999999    2453193
AAAAZG               9999999999    2453193

SQL>
As you can see, both rows have same values for ORDER BY expressions therefore you can't say which one is first and which one is second. Now it all depends how Oracle will process the query. I hope it is clear now. If you want AAAAZG to appear first since 'AAAAZG' > 'AAAAZF' then add it to ORDER BY:

select  *
  from  (
         SELECT  ROW_NUMBER() OVER(ORDER BY NVL(num_1,9999999999) DESC,NVL(num_2,9999999999) DESC,TEXT DESC) row_num,
                 text,
                 num_1,
                 num_2
           FROM  test_rownum
        )
  where row_num = 1 
/

   ROW_NUM TEXT                      NUM_1      NUM_2
---------- -------------------- ---------- ----------
         1 AAAAZG                             2453193

SQL>
Just keep in mind strings are compared left-to-right, so 'B' is greater that 'ABCDEF'.

SY.
Re: ROW_NUMBER() not working properly? [message #683572 is a reply to message #683552] Mon, 01 February 2021 10:44 Go to previous message
chachamenci
Messages: 2
Registered: January 2021
Junior Member
Thanks evreyone. Your comments were very helpful.
Previous Topic: Transfer the temporary file to the client machine after all files have been processed.
Next Topic: Alternative approach for a query run programmatically.
Goto Forum:
  


Current Time: Thu Apr 22 12:44:57 CDT 2021