How Does DISTINCT Interact with ROW_NUMBER() and How Can I Get Unique Row Numbers in SQL?

Understanding the DISTINCT Keyword and ROW_NUMBER() Function

When working with data in SQL, it often becomes necessary to retrieve only the unique rows from a table. The DISTINCT keyword is typically used for this purpose, as it eliminates duplicate rows from the result set. However, when combined with the ROW_NUMBER() function, the DISTINCT keyword may behave differently.

ROW_NUMBER() Function and Distinct Values

The ROW_NUMBER() function assigns a unique sequential number to each row in a table. By default, the rows are ordered by their position in the table. However, you can specify a different sorting order using the ORDER BY clause.

In the provided query, the ROW_NUMBER() function is used alongside the DISTINCT keyword:

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

This query aims to display all row numbers of unique values in the id column. However, it only returns the distinct values without showing the row numbers.

Solution Using DENSE_RANK() Function

To achieve the desired result, you can use the DENSE_RANK() function instead of ROW_NUMBER(). The DENSE_RANK() function assigns unique sequential numbers to each distinct value in a table, regardless of the order of the rows.

The corrected query using DENSE_RANK():

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

This query will return the distinct values in the id column along with their corresponding row numbers, fulfilling the original requirement.

The above is the detailed content of How Does DISTINCT Interact with ROW_NUMBER() and How Can I Get Unique Row Numbers in SQL?. For more information, please follow other related articles on the PHP Chinese website!