Find second highest salary from Employee table

There are several ways find second, third or fourth hightest record in a table. Let us create a Table to demonstrate this.

create table employee(name varchar(20), sal integer);
insert into employee values('Bharat', 1000);
insert into employee values('Lalit', 3000);
insert into employee values('Ravi', 2000);
insert into employee values('Jayant', 7000);
select * from employee;

So the data in our EMPLOYEE table is

NAME   SAL  
------ ---- 
bharat 1000 
lalit  3000 
ravi   2000 
Jayant 7000 

4 row(s) in 0 ms

Solutions below:
Solution1: Use not in clause:

select max(sal) from employee where sal not in (select max(sal) from employee);

Returns

3000

Solution 2:

select max(sal) from employee a where 1 = (select count(*) from employee b where b.sal > a.sal); 

Returns

3000

Solution 3: Use WHERE clause

select max(sal) from employee where sal < ( select max(sal) from employee);

Returns

3000
Advertisements

, , ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: