ROW_NUMBER with partition by returns result with duplicate rows

Question

When I run this query I get more rows then 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?

Answer 1

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