sqloracle-databaserow-numberpartition-by

ROW_NUMBER with partition by returns result with duplicate rows

When I run this query I get more rows than from a similar query without the ROW_NUMBER () line:

SELECT DISTINCT id, value,  
    ROW_NUMBER ()  OVER (PARTITION BY  (id)
                         ORDER BY  value  DESC NULLS LAST ) max  
FROM TABLE1 
WHERE id like '%1260' ORDER BY id ASC

VS

SELECT DISTINCT id, value
FROM TABLE1 
WHERE id like '%1260' ORDER BY id ASC

Why does it happen and how to fix it?

Solution

  • Think of it this way: if you have two rows with the same id and value, the second query gives you one row with the distinct id, value pair. The first gives you two rows, one with row_number() of 1 and the other with row_number() of 2.

    For the following data:

    ID   VALUE
--   -----
1    XXX
1    XXX

    Query 1 would return

    ID  VALUE   MAX
--  -----   ---
1   XXX      1
1   XXX      2

    Query 2 would return 

    ID  VALUE
--  -----
1   XXX