How to resolve duplicate values when sorting by Oracle ROWNUM?

When using ROWNUM to sort the result set, there may be duplicate values because ROWNUM is calculated after the query results are returned, so it is not possible to determine the ROWNUM value for each row before sorting. To solve this issue, a subquery can be used. For example, if you have a table named “employees” with a column named “salary” and you want to sort the salaries and retrieve the top 10 employees:

SELECT * FROM (
  SELECT * FROM employees ORDER BY salary DESC
) WHERE ROWNUM <= 10;

First, the query sorts the salaries in descending order, then uses the results as a subquery, and filters using ROWNUM. This ensures the correct sorting of the results and allows you to retrieve data for the top 10 employees. It is important to note that if you want to get unique results, you can use the DISTINCT keyword. For example:

SELECT DISTINCT salary FROM (
  SELECT * FROM employees ORDER BY salary DESC
) WHERE ROWNUM <= 10;

This will ensure that the resulting values only include non-repeating salaries.