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

SQL Query to Get Distinct Row Numbers with DENSE_RANK()

In SQL, finding unique (distinct) values is typically done using the DISTINCT keyword. However, when you need to display row numbers for these distinct values, using DISTINCT alone may not suffice.

In the given code:

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

The DISTINCT keyword removes duplicate id values, but the ROW_NUMBER() function assigns a unique row number to each record in the result set. So, it's not useful for identifying distinct values.

To achieve the desired result, we can use the DENSE_RANK() function instead:

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

The DENSE_RANK() function assigns a distinct rank to each unique id value, disregarding any gaps in the row numbers. This provides the unique row numbers for the distinct values in the result set.

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