Top N rows faqs

1. What is the difference between ROWNUM and FETCH FIRST N ROWS ONLY?

• ROWNUM is an older method used to limit the number of rows returned. 
• It assigns a number to each row returned by a query, and you can use it to limit the number of rows.
• However, it is tricky to use with ORDER BY because ROWNUM is assigned before the sorting occurs.
• FETCH FIRST N ROWS ONLY is a more modern and intuitive SQL syntax introduced in Oracle 12c.
• It directly limits the result set to the top N rows after applying the ORDER BY clause, making it easier to use and more readable.

2. Can I use ROWNUM with ORDER BY directly?

• No, you cannot directly use ROWNUM with ORDER BY in a single query because ROWNUM is assigned before the ORDER BY is processed.
• To apply sorting with ROWNUM, you need to use a subquery, where the sorting happens first, followed by applying ROWNUM.

Example:

SELECT *

FROM (

  SELECT * FROM employees ORDER BY salary DESC

)

WHERE ROWNUM <= 10;

3. How do I fetch rows starting from a specific point (pagination)?

• Starting with Oracle 12c, you can use the OFFSET and FETCH clauses to implement pagination.
• This allows you to skip a certain number of rows and then fetch the next N rows.

Example for pagination:

SELECT *

FROM employees

ORDER BY salary DESC

OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

• This will skip the first 20 rows and then fetch the next 10 rows.

4. What happens if two rows have the same value in the column I'm ordering by?

• If you use ROWNUM or FETCH FIRST N ROWS ONLY, Oracle will return rows based on the query’s sorting, but identical values may appear in any order unless a more specific method (like ROW_NUMBER() or DENSE_RANK()) is used to assign unique row numbers or ranks.
• DENSE_RANK() assigns the same rank to identical values but does not skip subsequent ranks, whereas ROW_NUMBER() assigns a unique number to each row, even if the values are identical.

5. Can I use ROWNUM in combination with DENSE_RANK() or ROW_NUMBER()?

• Yes, you can. In some cases, you may need to create an analytical query that ranks or numbers the rows in a result set using ROW_NUMBER() or DENSE_RANK() and then filter by that rank/row number in an outer query.

Example:

SELECT *

FROM (

  SELECT employees.*, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num

  FROM employees

)

WHERE row_num <= 10;

6. How can I get the top N rows ordered by multiple columns?

• You can use the ORDER BY clause to order the rows by multiple columns. If you're using ROWNUM or FETCH, just list the columns in the ORDER BY clause.

Example:

SELECT *

FROM employees

ORDER BY department_id, salary DESC

FETCH FIRST 10 ROWS ONLY;

• This will return the top 10 employees, ordered first by department_id and then by salary in descending order.

7. What is the performance impact of using ROWNUM and FETCH FIRST N ROWS ONLY?

• FETCH FIRST N ROWS ONLY is generally more efficient and easier to use compared to ROWNUM, especially in Oracle 12c and later. It directly applies row limiting and sorting.
• ROWNUM may require subqueries for proper sorting and can be less efficient in such cases.
• When using ORDER BY, make sure to have proper indexes on the columns being ordered, as this will optimize the query's performance.

8. Can I get "Top N rows" for each group of rows (e.g., for each department or category)?

• Yes, you can use the ROW_NUMBER() or RANK() functions along with a PARTITION BY clause to get the top N rows for each group.

Example to get the top 3 highest-paid employees per department:

SELECT *

FROM (

  SELECT employees.*, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num

  FROM employees

)

WHERE row_num <= 3;

• This will return the top 3 employees for each department, ordered by salary.

9. What if I want the top N rows with ties?

• If you want to include ties in your results (e.g., two employees earning the same salary), use DENSE_RANK() or RANK() instead of ROW_NUMBER(). ROW_NUMBER() will always return unique row numbers, while DENSE_RANK() and RANK() will assign the same rank to identical values.

Example with DENSE_RANK():

SELECT *

FROM (

  SELECT employees.*, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank

  FROM employees

)

WHERE rank <= 10;

• This ensures that employees with the same salary will receive the same rank, and the query will return the top 10 employees with possible ties.

10. Can I use FETCH with pagination in Oracle versions before 12c?

• No, FETCH and OFFSET are only available in Oracle 12c and later. In earlier versions, you would need to use ROWNUM or a ROW_NUMBER() approach to handle pagination.

Example for pagination in older versions using ROW_NUMBER():

SELECT *

FROM (

  SELECT employees.*, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num

  FROM employees

)

WHERE row_num BETWEEN 11 AND 20;

• This query fetches rows 11 to 20 based on salary ordering.