How to find particular highest salary from a table...
Here we discuss about to find "x" highest salary from a table..
There are so many options are available in sql server but this is best and easy way to find particular highest and lowest salary from a table...
Now-a-days many of our interviewers asking how to get 3rd highest salary from a table?
We just answering like this...SELECT MIN(SALARY) FROM SAMPLETABLE1 WHERE SALARY in (SELECT TOP 3 SALARY FROM SAMPLETABLE1)
But this is not a correct way and we never get the exact result by using this query...
Let us see...
Here we create one sample table like SAMPLETABLE1CREATE TABLE SAMPLETABLE1 (ID INT IDENTITY,NAME VARCHAR(100),SALARY MONEY)
and insert some values into it...INSERT INTO SAMPLETABLE1(NAME,SALARY) VALUES ('John',10000),
('Cate',8000),
('Arun',4000),
('Billy',8000),
('Raja',6000),
('Kumar',8000),
('William',9000),
('Devi',3000)
Then we write a simple query like this...
For Find "x" Highest salary
--=========================SELECT * FROM SAMPLETABLE1 A WHERE (SELECT COUNT(DISTINCT(SALARY))
FROM SAMPLETABLE1 B WHERE A.SALARY<=B.SALARY)=3
Here 3 is third highest salary from a table it will display
OUTPUT
--------
ID NAME SALARY
-- ------ -------
2 Cate 8000.00
4 Billy 8000.00
6 Kumar 8000.00
If we change into 5 then it will display fifth highest salary from a table (i.e)SELECT * FROM SAMPLETABLE1 A WHERE (SELECT COUNT(DISTINCT(SALARY))
FROM SAMPLETABLE1 B WHERE A.SALARY<=B.SALARY)=5
OUTPUT
--------
ID NAME SALARY
-- ------ -------
3 Arun 4000.00
If we change into 1 then it will display First Highest salary from a table (i.e)SELECT * FROM SAMPLETABLE1 A WHERE (SELECT COUNT(DISTINCT(SALARY))
FROM SAMPLETABLE1 B WHERE A.SALARY<=B.SALARY)=1
OUTPUT
--------
ID NAME SALARY
-- ------ -------
1 John 10000.00
this is the simple method to find all the highest value from a table..
For Find "x" Lowest salary
--========================
To find first lowest salary no need to write a new query just change the symbol from "<" to ">"
(i.e)SELECT * FROM SAMPLETABLE1 A WHERE (SELECT COUNT(DISTINCT(SALARY))
FROM SAMPLETABLE1 B WHERE A.SALARY>=B.SALARY)=1
it will display first lowest salary from a table
OUTPUT
--------
ID NAME SALARY
-- ------ -------
8 Devi 3000.00
To find second lowest salary just change "1" to "2" in our query....SELECT * FROM SAMPLETABLE1 A WHERE (SELECT COUNT(DISTINCT(SALARY))
FROM SAMPLETABLE1 B WHERE A.SALARY>=B.SALARY)=2
we get
OUTPUT
--------
ID NAME SALARY
-- ------ -------
3 Arun 4000.00
likewise we change numbers from our query to find all level salaries..SELECT * FROM SAMPLETABLE1 A WHERE (SELECT COUNT(DISTINCT(SALARY))
FROM SAMPLETABLE1 B WHERE A.SALARY>=B.SALARY)=3
OUTPUT
--------
ID NAME SALARY
-- ------ -------
5 Raja 6000.00
Another simple option is SELECT NAME,SALARY FROM(
SELECT DENSE_RANK() OVER(ORDER BY SALARY DESC) AS HighestSalary,NAME,SALARY FROM SAMPLETABLE1)A
WHERE HighestSalary=3
OUTPUT
--------
ID NAME SALARY
-- ------ -------
2 Cate 8000.00
4 Billy 8000.00
6 Kumar 8000.00
Here too we get the same result....
DROP TABLE SAMPLETABLE1
Hope it is useful to all the beginners!!!!
Thanks and Regards,
Sri
Senior DB Developer,
99 44 11 12 13