Hello Everyone , Hope all are doing good.
Here I am going to write the queries for finding the 1st ,2nd and 3rd highest salary details .
Here is the table which i am using to find out the required information.
ID NAME AGE ADDRESS SALARY
1
ramesh
32
Ahmedabad
2000
2
Kumar
31
Tirupati
4000
3
akaya
26
Hyderabad
2600
4
Chaitali
25
Mumbai
6500
5
Hardik
27
Bhopal
8500
6
kalyani
28
Pune
4500
7
Huffy
24
UP
10000
select * from Customer order by SALARY desc
ID NAME AGE ADDRESS SALARY
7 Huffy 24 UP 10000
5 Hardik 27 Bhopal 8500
4 Chaitali 25 Mumbai 6500
6 kalyani 28 Pune 4500
2 Kumar 31 Tirupati 4000
3 akaya 26 Hyderabad 2600
1 ramesh 32 Ahmedabad 2000
There are different ways to find the highest salary details .
1)select * from customer where salary=(select max(salary) from Customer)
2) select * from customer where salary =( select distinct top 1 salary from Customer order by SALARY desc)
ID NAME AGE ADDRESS SALARY
7
Huffy
24
UP
10000
find out 2nd highest salary
1)select * from Customer where SALARY=(select max(salary) from Customer where salary not in (select max(salary) from customer))
2) select * from customer where salary=(select min(salary) from customer where salary in (select distinct top 2 salary from Customer order by SALARY desc))
ID NAME AGE ADDRESS SALARY
5
Hardik
27
Bhopal
8500
find out 3rd highest Salary
1) select * from customer where salary=(select min(salary) from customer where salary in
(select distinct top 3 salary from Customer order by SALARY desc))
2)select * from customer where salary=(select max(salary) from Customer where salary < (select max(salary) from Customer where salary < (select max(salary) from Customer)))
ID NAME AGE ADDRESS SALARY
4
Chaitali
25
Mumbai
6500
find out 4th highest SALARY
1) select * from customer where salary=(select max(salary) from Customer where salary < (select max(salary) from Customer where salary < (select max(salary) from Customer where salary < (select max(salary) from Customer))))
ID NAME AGE ADDRESS SALARY
6
kalyani
28
Pune
4500
Next post , I will write queries for finding the 1st, 2nd and 3rd lowest salary details.
Thanks
ASR