How to Get Row Numbers for Distinct Values in SQL Using DENSE_RANK()?

Distinguishing Rows with Distinct Values Using Row_Number in SQL

The DISTINCT keyword in SQL plays a crucial role in eliminating duplicate rows when retrieving data. However, it may not suffice when you need to identify the unique occurrence of each value within a column. This is where Row_Number comes into play, but its combination with DISTINCT can be tricky.

The query in question attempts to display row numbers for distinct values in the id column:

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

However, this query results in the distinct values of id, not the row numbers for each unique value. To achieve the desired result, you can use the DENSE_RANK() function instead of DISTINCT:

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

DENSE_RANK() assigns sequential row numbers to distinct values, unlike ROW_NUMBER() which assigns gaps to duplicate values. By combining DENSE_RANK() with DISTINCT, you can retrieve the row numbers for each unique id value, providing the necessary information for further analysis or processing.

The above is the detailed content of How to Get Row Numbers for Distinct Values in SQL Using DENSE_RANK()?. For more information, please follow other related articles on the PHP Chinese website!