[sql] sql query distinct with Row_Number

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.

This question is related to sql distinct

The answer is


Try this:

;WITH CTE AS (
               SELECT DISTINCT id FROM table WHERE fid = 64
             )
SELECT id, ROW_NUMBER() OVER (ORDER BY  id) AS RowNum
  FROM cte
 WHERE fid = 64

This article covers an interesting relationship between ROW_NUMBER() and DENSE_RANK() (the RANK() function is not treated specifically). When you need a generated ROW_NUMBER() on a SELECT DISTINCT statement, the ROW_NUMBER() will produce distinct values before they are removed by the DISTINCT keyword. E.g. this query

SELECT DISTINCT
  v, 
  ROW_NUMBER() OVER (ORDER BY v) row_number
FROM t
ORDER BY v, row_number

... might produce this result (DISTINCT has no effect):

+---+------------+
| V | ROW_NUMBER |
+---+------------+
| a |          1 |
| a |          2 |
| a |          3 |
| b |          4 |
| c |          5 |
| c |          6 |
| d |          7 |
| e |          8 |
+---+------------+

Whereas this query:

SELECT DISTINCT
  v, 
  DENSE_RANK() OVER (ORDER BY v) row_number
FROM t
ORDER BY v, row_number

... produces what you probably want in this case:

+---+------------+
| V | ROW_NUMBER |
+---+------------+
| a |          1 |
| b |          2 |
| c |          3 |
| d |          4 |
| e |          5 |
+---+------------+

Note that the ORDER BY clause of the DENSE_RANK() function will need all other columns from the SELECT DISTINCT clause to work properly.

All three functions in comparison

Using PostgreSQL / Sybase / SQL standard syntax (WINDOW clause):

SELECT
  v,
  ROW_NUMBER() OVER (window) row_number,
  RANK()       OVER (window) rank,
  DENSE_RANK() OVER (window) dense_rank
FROM t
WINDOW window AS (ORDER BY v)
ORDER BY v

... you'll get:

+---+------------+------+------------+
| V | ROW_NUMBER | RANK | DENSE_RANK |
+---+------------+------+------------+
| a |          1 |    1 |          1 |
| a |          2 |    1 |          1 |
| a |          3 |    1 |          1 |
| b |          4 |    4 |          2 |
| c |          5 |    5 |          3 |
| c |          6 |    5 |          3 |
| d |          7 |    7 |          4 |
| e |          8 |    8 |          5 |
+---+------------+------+------------+

Using DISTINCT causes issues as you add fields and it can also mask problems in your select. Use GROUP BY as an alternative like this:

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

Then you can add other interesting information from your select like this:

,count(*) as thecount

or

,max(description) as description

How about something like

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

SQL Fiddle DEMO

You could also try

SELECT distinct id, DENSE_RANK() OVER (ORDER BY  id) AS RowNum
FROM @mytable
where fid = 64

SQL Fiddle DEMO


Question is too old and my answer might not add much but here are my two cents for making query a little useful:

;WITH DistinctRecords AS (
    SELECT  DISTINCT [col1,col2,col3,..] 
    FROM    tableName 
    where   [my condition]
), 
serialize AS (
   SELECT
    ROW_NUMBER() OVER (PARTITION BY [colNameAsNeeded] ORDER BY  [colNameNeeded]) AS Sr,*
    FROM    DistinctRecords 
)
SELECT * FROM serialize 

Usefulness of using two cte's lies in the fact that now you can use serialized record much easily in your query and do count(*) etc very easily.

DistinctRecords will select all distinct records and serialize apply serial numbers to distinct records. after wards you can use final serialized result for your purposes without clutter.

Partition By might not be needed in most cases


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

Try this

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

Or use RANK() instead of row number and select records DISTINCT rank

SELECT id
FROM  (SELECT id, ROW_NUMBER() OVER (PARTITION BY  id ORDER BY  id) AS RowNum
      FROM table
      WHERE fid = 64) t
WHERE t.RowNum=1

This also returns the distinct ids