• Register
First time here? Checkout the FAQ!
x
Welcome to Find4Answers.com

Where you can Ask Questions, Find Answers Or Receive Answers from other members of the community And Share in Social networking sites like facebook, linkedin, twitter.
3cx phone system assignment writing service bandar bola best waikiki restaurants boiler business car car insurance quotes car insurance quotes car insurance quotes car lease deals car leasing uk chwilówka przez internet zapewne company convey convey law complaints convey law reviews convey law service conveylaw cosmetic dentist csr classics hack csr classics hack csr classics hack data data recovery maidenhead deals design development double glazing leeds electrician electrician manchester electricians emergency farm filmy bez limitu filmy online finlock finlock solutions general genral golf holidays spain graduate jobs in london graduate jobs london heap how to jump higher how to jump higher how to jump higher infrared sauna infrared sauna saunas inline skating java judi bola law law firm in leeds lease leasing led lights led bulbs leeds legal smoke life insurance quotes log london manchester mercedes lease deals mercedes leasing mezzanine movies museums and art nikogo szybka chwilówka none none none ny male revue outsource link building paid search agency performance car hire personal personal injury lawyer porcelain veneers ramię szybkie chwilówki recovery restaurant seo seo agencies seo company seo company london seo las vegas seo services services sky diamond seo solutions steel synchronization szybka chwilówka przykład szybka chwilówka żaden szybkie chwilówki lekko temp cover car insurance temp cover car insurance temp cover car insurance thread tymczasem chwilówki przez internet upvc windows manchester viagra kamagra videos vinyl flooring suppliers wait web development company window repairs london windows zobaczyć szybka chwilówka

What is the SQL query for employee having 20th Max salary?

0 votes
asked Nov 23, 2011 in Technology by anonymous
    

2 Answers

0 votes
Consider the following Employee table with a single column for salary.

+------+
| Sal  |
+------+
| 3500 |
| 2500 |
| 2500 |
| 5500 |
| 7500 |
+------+

The following query will return the Nth Maximum element.

select SAL from EMPLOYEE E1 where (N - 1) = (select count(distinct(SAL)) from EMPLOYEE E2
            where E2.SAL > E1.SAL )


For eg. when the second maximum value is required,

  select SAL from EMPLOYEEE1 where ( 2 - 1 ) = (select count(distinct(SAL))
                from EMPLOYEE E2
                where E2.SAL > E1.SAL )


+------+
| Sal  |
+------+
| 5500 |
+------+

If you want for 20th max, use 20 inplace of N.

Explanation:

E1.sal ----- E2.sal(Note: E2.sal >E1.sal)

3500 5500
3500 7500
2500 3500
2500 5500
2500 7500
5500 7500
 

and count will be on E1.sal

The above query will work in oracle, not sure about SQL Server.

Try and let us know.
answered Nov 23, 2011 by sarada Hot Users (2,340 points)
edited Nov 24, 2011 by sarada
0 votes

TO find out 3rd highest salary from table

	--Find 3rd highest salary
	SELECT TOP 1 salary
	FROM (
	SELECT DISTINCT TOP 3 salary
	FROM tblSalary
	ORDER BY salary DESC) S
	ORDER BY salary
	

General form to find to Nth highest salary from table
	--Find Nth highest salary
	SELECT TOP 1 salary FROM (
	SELECT DISTINCT TOP N salary FROM tblSalary ORDER BY salary DESC) S
	ORDER BY salary
	

There are many solution to solve to this but above solution is easiest.
Take other possible solution,

	SELECT MIN(salary) FROM tblSalary WHERE salary IN
	(SELECT DISTINCT TOP 3 salary FROM tblSalary ORDER BY salary DESC)
	
	--or--
	
	SELECT MIN(salary) FROM
	(SELECT DISTINCT TOP 3 salary FROM tblSalary ORDER BY salary DESC) S

	

answered Nov 24, 2011 by anonymous
...