Home > Database > Mysql Tutorial > How to Select the Top 10 Oracle Records While Excluding Specific Dates?

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

Mary-Kate Olsen
Release: 2025-01-18 15:36:10
Original
306 people have browsed it

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:

<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>
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template