Oracle implements two methods of querying the first N records

introduction

In Oracle databases, querying the first N records in a table is a common requirement. Whether you need to get the latest data or do paging queries, mastering this technique is crucial. This article will introduce two commonly used methods to retrieve the first N records in Oracle tables and provide examples separately so that you can adjust them according to actual needs.

1. Use ROWNUM pseudo-column

ROWNUMis a pseudo column provided by Oracle that assigns a unique number to each row in the query result set, incrementing from 1. This method works for all Oracle versions.

SELECT *
FROM your_table
WHERE ROWNUM <= :N;

In this query,:Nis a placeholder that represents the number of records you want to retrieve. For example, if you want the first 50 records,:NReplace it with 50.

However, when you need to sort the first N records based on a column, you need to use a subquery to ensureROWNUMApplied after sorting.

SELECT *
FROM (
    SELECT *
    FROM your_table
    ORDER BY some_column
)
WHERE ROWNUM <= :N;

In this example, the internal query is first based onsome_columnrightyour_tableSort, and then the external query passesROWNUMThe size of the limit result set is the first N bars.

2. Use the FETCH FIRST clause (Oracle 12c and later)

Starting with Oracle 12c version, you can useFETCH FIRSTClauses to directly limit the size of the query result set. This method is more intuitive and easy to understand.

SELECT *
FROM your_table
ORDER BY some_column
FETCH FIRST :N ROWS ONLY;

same,:Nis a placeholder that represents the number of records you want to retrieve. This query is based onsome_columnrightyour_tableSort and then useFETCH FIRST :N ROWS ONLYTo limit the result set to the first N records.

3. Practical application examples

Suppose we have a name calledemployeestable, and we want to presssalaryThe descending order of the column gets the records of the first N employees.

useROWNUMExamples：

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

useFETCH FIRSTExample (for Oracle 12c and later)：

SELECT *
FROM employees
ORDER BY salary DESC
FETCH FIRST :N ROWS ONLY;

In these two examples, you just need to put:NReplace with the number of records you want to retrieve.

in conclusion

Depending on your Oracle database version and specific requirements, you can choose to use the ROWNUM or FETCH FIRST clause to query the first N records. For Oracle 12c and later, the FETCH FIRST clause is usually a more concise and intuitive choice. For earlier versions of Oracle, using the ROWNUM method with subqueries is a reliable solution.

The above is the detailed content of Oracle's two methods of querying N records before queries. For more information about Oracle's querying N records before queries, please pay attention to my other related articles!