CODEWITHSUNDEEP
sqloracle-databaserow-numberpartition-by
user1658192
user1658192

Reputation: 231

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?

Upvotes: 8

Views: 47432

Answers (3)

John Woo
John Woo

Reputation: 263703

If you are using Oracle 11g R2, try this.

WITH DistinctRow 
AS
(
     SELECT DISTINCT id, value
     FROM TABLE1 
     WHERE id like '%1260'
)
SELECT id, value,  
       ROW_NUMBER() over (partition by (id) ORDER BY value desc NULLS LAST) max  
FROM DistinctRow 
ORDER BY max ASC

Upvotes: 0

DCookie
DCookie

Reputation: 43523

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

Upvotes: 5

user743382
user743382

Reputation:

The rows are no longer the same because you added a row number, so DISTINCT doesn't do anything. One way to avoid this is to add your row number after you've used DISTINCT.

SELECT id, value, ROW_NUMBER ()   over (partition by  (id)   ORDER BY  value  desc NULLS LAST  ) max
FROM (
    SELECT DISTINCT id, value
    FROM TABLE1 
    WHERE id like '%1260'
) AS subquery
ORDER BY id ASC

(I'm not sure if the syntax is right for Oracle, it may need minor tweaking.)

Upvotes: 6

Related Questions