How to find second highest salary of a given table in oracle database

Answer 1

SELECT max(salary) FROM Employee WHEREsalary < (SELECT max(salary) FROM Employee);

Answer 2

Sorry sorry it's execute thank u so much Adarsh Srivastava 🙏🙏🙏

Answer 3

Adarsh Srivastava only second highest salary..
If we used this command then all rows selected expected highest salary of a table..

Answer 4

Mind that this is an untested query, an alternative by using ROWNUM limiter:

SELECT salary FROM payroll WHERE ROWNUM = 2 ORDER BY salary DESC;

Answer 5

Ipang If this doesn't work, the ORDER BY clause may need to be applied as a subquery.

Example:

SELECT * FROM
( SELECT salary
FROM payroll
ORDER BY salary DESC )
WHERE ROWNUM = 2;

Answer 6

[aside] Is this homework? I ask because I see this question semi-regularly with minor changes that don't make the problem that's actually being solved unique.

One top-level abstraction could cover this case and numerous variations (vs the one-off's), but the value of an abstraction depends on whether this is likely to keep repeating.

Answer 7

Yes David Carroll that probably how I should've written it in the first place : )
I can't test it though, nowhere near any server, but your query looks more selective than my proposal.

Thanks David ; )

Answer 8

Jyoti I dont think so....It will give 2nd highest salary surely..

Answer 9

Jyoti no need to be sorry...My pleasure

Answer 10

Not a comment on the challenge or in a working technique, just on how I write some queries.
I have good success using Common Table Expressions (CTE). It's a formalized technique to combine more than one query as named entities.
It's usually pretty fast for me to come up with good answers to complex queries.
Opening word is WITH:
WITH name1 AS ( query_1 ), name2 AS ( query_2 ) query_3 ;
If there are more queries to add, the process is repreated with all subqueries, and followed by the outside query.

WITH COSAL AS
( SELECT salary , empid, fname, lname, dept
FROM payroll
ORDER BY salary DESC
)
SELECT salary
FROM COSAL
WHERE ROWNUM = 2
;

I just find this format easier to read, construct, and use. If I need to design as I go, this is the simplest way for me to keep things straight.

Answer 11

Use ranking

select *
from ( select salary
, dense_rank() over (order by salary desc) ranking
from employee )
where ranking = 2 -- Replace 2 with any value of N

Answer 12

How do you mean pls

Answer 13

Select salary from (Select salary from Employee order by salary desc limit 1) order by salary aesc limit 1

Answer 14

Select max(salary) from Employee where salary <>(Select max(salary)from Employee)

Answer 15

Тут есть русский

Answer 16

Nice joti