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:
<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 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;</code>
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 <= 10
constraint, ensuring only the top 10 records are returned. This method correctly applies the row limit after 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!