How to Assign Row Numbers to Distinct Values in SQL?

Distinct Row Numbers in SQL using Row_Number

Distinct queries in SQL are used to retrieve unique values from a specified column. However, there are scenarios where it's desirable to not only display distinct values but also their corresponding row numbers. To achieve this, the Row_Number function can be utilized in conjunction with the DISTINCT keyword.

In the example provided, the user attempted to combine DISTINCT with Row_Number to display distinct values along with row numbers:

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

However, this query yielded only distinct values, similar to the following query:

SELECT distinct id FROM table WHERE fid = 64

To resolve this issue, the DENSE_RANK function can be used instead of Row_Number. DENSE_RANK assigns consecutive row numbers to distinct values, regardless of any gaps in the data.

Here's the corrected query:

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

This query will provide the desired result: a list of distinct values with corresponding row numbers, as intended.

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