How to Get the Top 5 Highest Values from an Oracle SQL Column?

Oracle SQL: How to retrieve the top 5 highest values ​​in a column

In database queries, it is often necessary to limit the results to rows with specific characteristics, such as the highest or lowest value in a specific column. In Oracle SQL, you can use analytic functions such as RANK(), DENSE_RANK(), or ROW_NUMBER() for this purpose.

RANK() and DENSE_RANK() for highest values

Analysis functions like RANK() and DENSE_RANK() assign a rank to each row based on a specified ranking. To retrieve the top 5 highest values ​​in a column, you can use the following query format:

<code class="language-sql">SELECT *
FROM (
  SELECT <column_name>, RANK() OVER (ORDER BY <column_name> DESC) AS rnk
  FROM <table_name>
)
WHERE rnk <= 5;</code>

RANK() assigns a numerical rank to each row, starting with the highest value of 1. DENSE_RANK() compresses gaps when identical values ​​exist, while assigning ranks.

ROW_NUMBER() for a specific number of rows

ROW_NUMBER() analysis function assigns a serial number to each row. You can use this to retrieve a specific number of rows regardless of their value. For example:

<code class="language-sql">SELECT *
FROM (
  SELECT <column_name>, ROW_NUMBER() OVER (ORDER BY <column_name> DESC) AS rnk
  FROM <table_name>
)
WHERE rnk <= 5;</code>

Non-analytical solution: use ROWNUM

Although less common, you can also use the ROWNUM pseudo-column to limit the results to a specific number of rows. However, it should be used with caution because ROWNUM is evaluated before the ORDER BY clause, which can lead to unpredictable results.

Example: Get the highest paid employee

To illustrate the use of RANK() and DENSE_RANK() when retrieving the 5 highest paid employees:

<code class="language-sql">SELECT *
FROM (
  SELECT empno, sal, RANK() OVER (ORDER BY sal DESC) AS rnk
  FROM emp
)
WHERE rnk <= 5;</code>

This query assigns each employee a numerical rank based on their salary, where 1 represents the highest paid employee. It then displays the top 5 employees in order of decreasing salary.

The above is the detailed content of How to Get the Top 5 Highest Values from an Oracle SQL Column?. For more information, please follow other related articles on the PHP Chinese website!