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 ?

score 3 · Answer 1 · answered Jun 15 '15 at 13:19

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

score 1 · Answer 2 · answered Jun 15 '15 at 12:23

1

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

answered Jun 15 '15 at 12:23

Mukesh Kalgude

4,814
2
17
32

1

I appreciate your answer, but sub-query will reduce performance of application. So I have to find any other way. – unknown Jun 15 '15 at 12:27

score 0 · Answer 3 · answered Jun 15 '15 at 12:21

0

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.

answered Jun 15 '15 at 12:21

Hysteria86

367
1
10

I want to retrieve distinct data and apply row number on those records. – unknown Jun 15 '15 at 12:25

Row_Number in Select query with Distinct result

3 Answers3