How to Select the Top 10 Oracle Records Using a Subquery and Filtering?

Select top 10 records in Oracle using subquery and filtering

In Oracle database, you can use subquery and filtering techniques to select the top 10 records sorted by a specific column. The query originally used to select all matching records can be modified to include this requirement.

To solve the problem of returning "random" records, you can use the ROWNUM clause (which is the Oracle equivalent of the LIMIT clause).

Modified query:

<code class="language-sql">SELECT * 
FROM 
(
  SELECT DISTINCT 
    APP_ID, 
    NAME, 
    STORAGE_GB, 
    HISTORY_CREATED, 
    TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') AS HISTORY_DATE  
  FROM HISTORY 
  WHERE 
    STORAGE_GB IS NOT NULL AND 
    APP_ID NOT IN (
      SELECT APP_ID 
      FROM HISTORY 
      WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') = '06.02.2009'
    )
  ORDER BY 
    STORAGE_GB DESC 
) 
WHERE 
  ROWNUM <= 10;</code>

This query first uses a subquery to retrieve matching records. It then applies the ROWNUM clause to select the top 10 records from the resulting dataset based on the sorting criteria.

Performance Notes:

If query performance is slow when processing large data sets, consider using NOT EXISTS instead of NOT IN for the subquery predicate. NOT EXISTS may be more efficient when the subquery contains a large number of records.

The above is the detailed content of How to Select the Top 10 Oracle Records Using a Subquery and Filtering?. For more information, please follow other related articles on the PHP Chinese website!