Row_Number in Select query with Distinct result

Question

I have a query in SQL Server 2012:-

SELECT DISTINCT 
    FIELD_1, FIELD_2
FROM 
    TABLE_A

Now I want to get row number for each record. So I have used ROW_NUMBER() function for this purpose.

SELECT DISTINCT 
    ROW_NUMBER() OVER (ORDER BY FIELD_1, FIELD_2) AS rowId
    FIELD_1, FIELD_2
FROM 
    TABLE_A

This query does not give me distinct result.
How to retrieve distinct result with row number ?

Answer 1

When you are using DISTINCT you should not forget that it will apply after window functions like ROW_NUMBER that makes all your rows unique.

So try this:

SELECT 
    ROW_NUMBER() OVER (ORDER BY FIELD_1, FIELD_2) AS rowId,
    FIELD_1, FIELD_2
FROM 
    TABLE_A
GROUP BY
    FIELD_1, FIELD_2

Answer 2

You are selecting DISTINCT ROW_NUMBER(). The row numbers will obviously be unique (e.g. distinct), so this won't really work for what you are trying to achieve.

Answer 3

try to this

SELECT 
ROW_NUMBER() OVER (ORDER BY FIELD_1, FIELD_2) AS rowId
FIELD_1, FIELD_2
FROM (select DISTINCT FIELD_1, FIELD_2 TABLE_A ) a