Fixed SQL Query: Adding Group By When Using ROW_NUMBER ( )

If we have ROW_NUMBER ( )  in SQL query, using SELECT DISTINCT shows the duplicate record.

SQL query

SELECT DISTINCT

COL1 AS ID,

ROW_NUMBER( ) OVER( ORDER BY COL1) AS RowNum,

COL2 AS FirstName

FROM table

INNER JOIN table ON xx=xx

INNER JOIN table ON xx=xx

INNER JOIN table ON xx=xx

INNER JOIN table ON xx=xx

WHERE ...

Result:

  

How can I remove the duplicate record when using ROW_NUMBER( ) in SQL query? The answer is to use Group By.

Modify SQL query: adding Group By

SELECT

COL1 AS ID,

ROW_NUMBER( ) OVER( ORDER BY COL1) AS RowNum,

COL2 AS FirstName

FROM table

INNER JOIN table ON xx=xx

INNER JOIN table ON xx=xx

INNER JOIN table ON xx=xx

INNER JOIN table ON xx=xx

WHERE ...

GROUP BY COL1, COL2

Result: