How to find the second highest salary in Oracle
To find the second highest salary in Oracle, the most commonly used methods are: 1. Use ROW_NUMBER() or RANK(), where ROW_NUMBER() assigns a unique sequence number to each row, which is suitable for obtaining the second row of data, and RANK() will skip subsequent rankings when processing the juxtaposition; 2. Use MAX() and subqueries to directly obtain the maximum value below the highest salary through SELECT MAX(salary) FROM employees WHERE salary
To find the second highest salary in Oracle, you can use several approaches. Here are the most common and effective methods:
1. Using ROW_NUMBER() (or RANK() )
This method assigns a rank to each salary in descending order and then filters for the second highest.
SELECT sales
FROM (
SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
)
WHERE rn = 2;-
ROW_NUMBER()assigns unique sequential numbers, even if salaries are tied. - If you want to handle ties (ie, same salary values), use
RANK()instead — but note thatRANK()may skip ranks after ties.
Example with RANK() :
SELECT sales
FROM (
SELECT salary, RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
)
WHERE rnk = 2;Use
ROW_NUMBER()if you want exactly the second distinct position, regardless of duplicates. UseRANK()if you want true ranking (eg, if two people have the highest salary, the next is third, not second).
2. Using MAX() and Subquery
A simple approach without window functions:
SELECT MAX(salary) AS second_highest_salary FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);
- This finds the maximum salary, then gets the max salary that is less than that.
- It naturally gives you the second highest distinct salary.
- Works well and is easy to understand.
3. Using DENSE_RANK() (Best for Handling Duplicates)
If multiple employees have the same salary and you want the second highest distinct value (without gaps in ranking), use DENSE_RANK() .
SELECT sales
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees
)
WHERE drnk = 2;- Unlike
RANK(),DENSE_RANK()doesn't skip ranks. So if two people are tied for first, the next distinct salary is ranked 2.
4. Using OFFSET and FETCH (Oracle 12c)
If you're on Oracle 12c or later, you can use OFFSET and FETCH :
SELECT sales FROM employees ORDER BY salary DESC OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;
- This skips the highest salary and picks the next one.
- It does not eliminate duplicates unless you use
DISTINCT.
To get the second highest distinct sales:
SELECT DISTINCT salary FROM employees ORDER BY salary DESC OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;
Summary
| Method | Handles Duplicates Well? | Notes |
|---|---|---|
ROW_NUMBER()
|
No — treatments ties as separate ranks | Good for exact second row |
RANK()
|
Partial — skips ranks after ties | May skip to 3rd if two 1st |
DENSE_RANK()
|
Yes — no gaps in ranking | Best for logical "second highest" |
MAX()
|
Yes — distinct values only | Simple, works on older versions |
OFFSET FETCH
|
With DISTINCT , yes |
Modern syntax, 12c only |
For most practical purposes, if you want the second highest distinct salary , the MAX() method or DENSE_RANK() is recommended.
Choose based on your Oracle version and whether you care about duplicate values.
Basically, it's not complicated — just pick the right tool for your data and requirements.
The above is the detailed content of How to find the second highest salary in Oracle. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
ArtGPT
AI image generator for creative art from text prompts.
Stock Market GPT
AI powered investment research for smarter decisions
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
How to check for NULL values in an Oracle query?
Sep 18, 2025 am 04:39 AM
UseISNULLorISNOTNULLtocheckforNULLvaluesinOracle;neveruse=or!=,asNULLrepresentsunknownandcannotbecomparedwithequalityoperators.
How to find the structure of a table in Oracle?
Sep 18, 2025 am 12:30 AM
Use the DESCRIBE command to quickly view table structures, including column names, data types, and null attributes; query the USER_TAB_COLUMNS view to obtain more detailed column information, such as length, precision, and decimal places; use ALL_TAB_COLUMNS to view tables owned by other users in a multi-user environment; use ALL_CONSTRAINTS and ALL_CONS_COLUMNS views to check primary keys, foreign keys, and constraint relationships to fully understand the structure of Oracle tables.
How to create a table in Oracle?
Sep 20, 2025 am 03:07 AM
Use the CREATETABLE statement to create a table, define the table name, column name and data type, and optionally add constraints; 2. Common data types include NUMBER, VARCHAR2, DATE, etc.; 3. Constraints such as PRIMARYKEY, NOTNULL, UNIQUE, CHECK, FOREIGNKEY to ensure data integrity; 4. CREATETABLEASSELECT can create tables based on query results and copy data, but the original table constraints are not automatically copied.
How to use the SOUNDEX function for phonetic searches in SQL?
Sep 21, 2025 am 01:54 AM
The SOUNDEX function converts text into a four-character code representing pronunciation, adds three digits to the first letter, ignores vowels and specific letters, and maps consonants with similar pronunciations to the same number, realizing pronunciation-based search. For example, Smith and Smythe both generate S530, and names with similar pronunciations can be found through WHERESOUNDEX(last_name)=SOUNDEX('Smith'). Combined with the DIFFERENCE function, it can return a similarity score of 0 to 4, filter the results of pronunciation close, which is suitable for dealing with spelling differences, but has limited effect on non-English names, and performance optimization needs to be paid attention to.
How to add a comment to a table or column in SQL?
Sep 21, 2025 am 05:22 AM
UseCOMMENTONCOLUMNorALTERTABLEwithCOMMENTtodocumenttablesandcolumnsinSQL;syntaxvariesbyDBMS—PostgreSQLandOracleuseCOMMENTON,MySQLusesCOMMENTinCREATE/ALTERstatements,andcommentscanbeviewedviasystemtableslikeINFORMATION_SCHEMA,butSQLitelackssupport.
How to get the last day of the month for a given date in SQL?
Sep 18, 2025 am 12:57 AM
Use the LAST_DAY() function (MySQL, Oracle) to directly obtain the last day of the month where the specified date is, such as LAST_DAY('2023-10-15') to return 2023-10-31; 2. SQLServer uses the EOMONTH() function to achieve the same function; 3. PostgreSQL calculates the end of the month through DATE_TRUNC and INTERVAL; 4. SQLite uses the date function to combine 'startofmonth', '1month' and '-1day' to obtain the results.
How to add a UNIQUE constraint to a SQL column?
Sep 24, 2025 am 04:27 AM
When using CREATETABLE, add UNIQUE keyword or use ALTERTABLEADDCONSTRAINT to add constraints to existing tables to ensure that the values in the column are unique, and support single columns or multiple columns. Before adding, you need to ensure that the data is not duplicated. You can delete it through DROPCONSTRAINT, pay attention to the syntax differences between different databases and NULL values.
How to update data in a table in SQL
Sep 18, 2025 am 06:04 AM
To update the data in the SQL table, the UPDATE statement is required. The basic syntax is: UPDATE table name SET column 1 = value 1, column 2 = value 2...WHERE conditions; 1. When updating a single row, specify a unique identifier, such as the primary key; 2. When updating multiple columns, list multiple columns and values in the SET clause; 3. When updating multiple rows, multiple records matching the WHERE conditions will be updated at the same time; 4. If the WHERE clause is omitted, all rows will be updated, so be careful to operate; 5. It is recommended to use transaction wrapping UPDATE operations to roll back when an error occurs; In addition, you should always use SELECT to test the conditions, backup important data, and use LIMIT in supported databases to limit the number of rows to be updated.
