How to Display Row Numbers for Unique Values in SQL?

Displaying Row Numbers of Unique Values using SQL

The DISTINCT keyword in SQL is used to retrieve only unique values from a specified column. In this particular case, you aim to display the row numbers of unique values in a column using the ROW_NUMBER() function.

However, the query you provided:

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

is not returning the expected results because it effectively eliminates duplicates using DISTINCT. As a result, it behaves similarly to the following query:

SELECT DISTINCT id FROM table WHERE fid = 64

To achieve your desired output, you can utilize the DENSE_RANK() function instead. DENSE_RANK() assigns unique row numbers to each distinct value in the specified order, even if there are duplicate values.

Here's the modified query:

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

This query will provide you with the row numbers corresponding to the unique values in the specified column for the rows satisfying the condition WHERE fid = 64.

The above is the detailed content of How to Display Row Numbers for Unique Values in SQL?. For more information, please follow other related articles on the PHP Chinese website!