How to Select the Top 10 Oracle Records While Excluding Specific Dates?

Oracle Top 10 Record Selection with Date Exclusion

This article addresses the common Oracle database challenge: retrieving the top 10 records ordered by a specific column, while simultaneously excluding records matching specific criteria from another table. A simple SELECT DISTINCT with an ORDER BY and ROWNUM clause won't suffice due to the interaction of these elements.

The solution utilizes a subquery to achieve the desired outcome:

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 NOT EXISTS (
      SELECT 1
      FROM HISTORY h2
      WHERE 
        h2.APP_ID = HISTORY.APP_ID
        AND TO_CHAR(h2.HISTORY_DATE, 'DD.MM.YYYY') ='06.02.2009'
    )
  ORDER BY STORAGE_GB DESC
)
WHERE 
  ROWNUM <= 10;

This approach first filters the HISTORY table within the inner query. The NOT EXISTS clause efficiently excludes records where APP_ID matches records with HISTORY_DATE equal to '06.02.2009'. The results are then ordered by STORAGE_GB (descending). The outer query applies the ROWNUM constraint, ensuring only the top 10 records are returned. This method correctly applies the row limit <em>after</em> the filtering and ordering, guaranteeing accurate results.

Note: NOT EXISTS generally offers better performance than NOT IN for this type of exclusion, particularly with large datasets. Consult Oracle documentation for further performance optimization strategies.

The above is the detailed content of How to Select the Top 10 Oracle Records While Excluding Specific Dates?. For more information, please follow other related articles on the PHP Chinese website!