sql query distinct with Row_Number

sql distinct

objectWithoutClass · Aug 8, 2013 · Viewed 112.1k times · Source

I am fighting with the distinct keyword in sql. I just want to display all row numbers of unique (distinct) values in a column & so I tried:

SELECT distinct id, ROW_NUMBER() OVER (ORDER BY  id) AS RowNum
                             FROM table
                             where fid = 64

however the below code giving me the distinct values:

SELECT distinct id FROM table where fid = 64

but when tried it with Row_Number.
then it is not working.

Answer

This can be done very simple, you were pretty close already

SELECT distinct id, DENSE_RANK() OVER (ORDER BY  id) AS RowNum
FROM table
WHERE fid = 64