The SQL statement how to write-CodePudding

+ - + -- -- -- -- -- -- -- -- +
| | Id Salary |
+ - + -- -- -- -- -- -- -- -- +
| | 100 | 1
200 | | 2 |
300 | | 3 |
+ - + -- -- -- -- -- -- -- -- +

To find out the second highest salary, thank you!

CodePudding user response:

 select id, salary 
The from (select id, salary, dense_rank () over (order by salary desc) rank the from table name) 
Where rank=2;

Look at your output, however, don't feel like oracle, don't know doesn't support this analysis function,

CodePudding user response:

 
SELECT * FROM TOP 1 (
SELECT the TOP 2 * FROM the TAB ORDER BY Salary DESC 
) AS a ORDER BY Salary

Oracle should also support this way

CodePudding user response:

refer to the second floor the rain stopped reply:

 
SELECT * FROM TOP 1 (
SELECT the TOP 2 * FROM the TAB ORDER BY Salary DESC 
) AS a ORDER BY Salary

This way should be oracle also support

What is this database syntax, not seen... Oracle does not support, 12 c, oracle, and mysql, postgresql and similar limit paging grammar, before 12 c, but need to borrow the pseudo column rownum to complete this function:

 select id, salary 
The from (select id, salary, rownum rn 
The from (select id, salary from the TAB order by salary desc)) 
Where an rn=2

CodePudding user response:

reference minsic78 reply: 3/f

Quote: refer to the second floor the rain stopped reply:

SELECT * FROM TOP 1 (
SELECT the TOP 2 * FROM the TAB ORDER BY Salary DESC
) AS a ORDER BY Salary

This way should be oracle also support

The grammar of the SQL server
CodePudding user response:
references the rain stopped, 4/f response:

Quote: refer to the third floor minsic78 response:

Quote: refer to the second floor of the rain stopped reply:

SELECT * FROM TOP 1 (
SELECT the TOP 2 * FROM the TAB ORDER BY Salary DESC
) AS a ORDER BY Salary

This way should be oracle also support

The grammar of the SQL server

The teach by