I have Faced this Quetion in Tech. Interview.It is vey nice solution,I was finding this solution since many days.

if there will present lots of employees who have same salary , then how will you get the 2nd hightest salaries of those employees.

Hi jaysh,

Good question. The answer to your question is really simple. If there were multiple employees having the same salaries, all you need to do is use group by in the inner query like this:

SELECT TOP 1 Salary FROM (SELECT TOP 2 Salary FROM Employee GROUP BY Salary ORDER BY Salary DESC) AS E ORDER BY Salary ASC

The inner query would now return only unique salaries in descending order and the outer query would then select the top 1 salary in reverse order based on the results from inner query.

Cheers,
Raj

~~~ CODING FOR ETERNITY !!! ~~~

You've been kicked (a good thing) - Trackback from DotNetKicks.com

Another excellent post, Raj. Thanks!

Joe

Hi Joe,

Thanks for your encouraging feedback mate. Cheers.

Cheers,
Raj

~~~ CODING FOR ETERNITY !!! ~~~

The answer given solves the solution, but with SQL 2005 being out now for a number of years the most correct solution should be given in T-SQL 2005 syntax.

WITH Customer AS (

SELECT *, ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNumber

FROM cus_Customer

)

SELECT *

FROM Customer

WHERE RowNumber = 2

This also allows extra variations such as:

WHERE RowNumber >= 20

WHERE RowNumber BETWEEN 5 AND 10

WHERE RowNumber in (1, 4, 5, 6, 7, 10, 15)

hi

Thanx for this answer , But i didnt get how will i write top 1 and top 2 salary in a query. I have written a query for this , please check and tell me on this query that how to get all the employee's name of same salary

SELECT max(SALARY) as Sal FROM tbl_tips WHERE SALARY<>(SELECT MAX(SALARY) FROM tbl_tips)

Hi Chris,

Thanks for the suggestion :-) Will update the above post to also include your example as well :-)

Cheers,
Raj

~~~ CODING FOR ETERNITY !!! ~~~

I am certainly not a SQL wizard, but please explain to me why the last ORDER BY is necessary.

Cant you just change the inner sub query to

"SELECT TOP 2 Salary FROM Employee ORDER BY Salary ASC"

and get

4000

5000

as the result, and from there just do the outer query without the ORDER BY, like so?

SELECT TOP 1 Salary FROM (SELECT TOP 2 Salary FROM Employee GROUP BY Salary ORDER BY Salary ASC)

This may be a simpler solution to Jaysh's question:

SELECT TOP 1 Salary

FROM Employee

WHERE Salary not in (

  SELECT top 1 Salary

  FROM Employee

  ORDER BY Salary DESC)

ORDER BY Salary DESC

Hi Egil,

You wrote: Cant you just change the inner sub query to "SELECT TOP 2 Salary FROM Employee ORDER BY Salary ASC" and get 4000, 5000.

My reply: No, the above inner query would return 1000, 2000 instead of 4000, 5000. Thats because we are selecting top 2 rows after sorting all records in ascending order (1-9).

Cheers,
Raj

~~~ CODING FOR ETERNITY !!! ~~~

Hi Rip,

Thanks for your contribution :-) However, if we have many employees sharing the same salary, your query would surely work as long as we are interested in second highest value. However the moment you need the third highest value or the fourth highest value, you will have no choice but to add the GROUP BY clause in your sub query :-)

Try to get the third highest salary by executing your query for the following records:

1000
2000
3000
4000
4000
5000
5000

Cheers,
Raj

~~~ CODING FOR ETERNITY !!! ~~~

ahh of course. should have figured that out by my self ;)

I have been giving this for a few yers in interviews, and recently had an answer that I have never seen before.

delete from Table

where salary = (Select max(salary)

                             from table )

select MAX(salary)

from table

(He got one of the other ways as well)... Just an interesting approach, No?

Hi Davod,

I could not stop laughing when I read your comment. I would love to meet that great developer who gave you that solution ;-) Just that I would never want to have him as a co developer on any of my projects ;-)

Cheers,
Raj

~~~ CODING FOR ETERNITY !!! ~~~

Pingback from  Daily Bits - February 26, 2008 | Alvin Ashcraft's Daily Geek Bits

LOL, the comment that contained the DELETE clause made me laugh like mad!!!!!!

Hi All,

Hows this one to find second Highest...

"select max(salary) from test where salary not in (select max(salary) from test)"

Hi Sumit,

Thanks for your contribution. Your query would work as long as we are interested in the second highest value. But it wont give you the flexibility to get the third or the fourth highest value. Rip posted a similar solution (see above in comments) but his solution too has the same limitations.

But its really interesting to read so many different queries people come up to solve this ;-)

Cheers,
Raj

~~~ CODING FOR ETERNITY !!! ~~~

Hi Raj,

Thanks for ur nice post for getting Second or any other highest from a Table. As i think there is a flaw in ur Query. If there are two salary having same amount, then ur query returns wrong result.

I tried   it for Salary like:

Salary

55000

55000

45000

35000

Now it will return 55000 if ur query executed and with my query it returns 45000 that is right i think.

Regards,

Sumit

sumitarora.bit@gmail.com

Hi Sumit,

Can you please check out the 3rd comment from the top in which I have replied to a question by jaysh? In short, if you have multiple employees sharing the same salary, you can use GROUP BY to get appropriate results.

Cheers,
Raj

~~~ CODING FOR ETERNITY !!! ~~~

Raj,

Gud work. and Thanks.

Sumit

Hi Raj,

can you also help in finding the 2nd highest salary, employee name from each department id. Suppose there are 10 departments, then 2nd highest salary in each department along with employee name.

Thanks

hey guys, as a newbie here and with T-SQL I have a question for the following query, which is the one from the top pf the page.

SELECT TOP 1 Salary

FROM (SELECT TOP 2 Salary FROM Employee

            ORDER BY Salary DESC) AS E

ORDER BY Salary ASC

what is the role here of the "AS E" and why it doesn't work if I avoid it? Why declaring that alias is necessary is what I'm asking

how to serch high secend employees by sql query

Hi d_ser,

Derived tables MUST always have an alias otherwise your queries wont work. Click here for detailed info on the same. Hope this helps.

Cheers,
Raj

~~~ CODING FOR ETERNITY !!! ~~~

thanx raj

thanks Raj.... i was searching that query to find out  2nd top data in a table. nice solutions given by u ....

Pingback from  tsql

Hey Raj...

Good Solution.....

What is the query to select the second last salary??

After reading my earlier post T-SQL Query to Find the Second Highest Column Value in a Table a lot of

Hi Dipti,

Please visit http://codeforeternity.com/blogs/technology/archive/2008/04/30/t-sql-query-to-find-the-second-lowest-column-value-in-a-table.aspx for the solution

Cheers,
Raj

~~~ CODING FOR ETERNITY !!! ~~~

Thanks a lot!!!..appreciate ur reply..

Please let me know the query for first five highest salary?

Thanks, for that. but how to select top 5 Rows i.e from top rows 6 to top rows10. I didn't want output of top 5 row and after top 10 rows.

Hi Depti,

Let's try this

SELECT TOP 5 Salary FROM Salary ORDER BY Salary desc

Vasant

I think it should work...

Hi Raj,

I found query.........

SELECT TOP 5 tradedate FROM(SELECT TOP 5 tradedate FROM (SELECT TOP 10 tradedate FROM dbo.tbbankloanDetail GROUP BY tradedate ORDER BY tradedate desc)

AS E ORDER BY tradedate asc)AS b ORDER BY tradedate desc

Plze try it, If you have other way please putt it.

Vasant Jagtap

hey how to find duplicate values in a column n how to delete duplicate rows

Nice post...however, there is another way of doing it too...lets assume we need 3rd highest salary in a table employee..we can use this

select min(Emp_Sal) from Employee_Test where Emp_Sal in

(select distinct top 3 Emp_Sal from Employee_Test order by Emp_Sal desc)

Hi Ching,

you can use this

select <Columnmame> from table group by <columnname> having count(*)>1

select e.salary from emp  e where 2 <(select count(distinct salary) from salary <e.salary)

with tmp as(

select SUM(purchase_price) as tp,SUM(purchase_price) as tp1 from dwpurchases dp, dwcustomer dc where dp.customer_key = dc.customer_key and dc.country = 'Australia' group by dc.state )

select dc.state from dwpurchases dp, dwcustomer dc where dp.customer_key = dc.customer_key and dc.country = 'Australia'

group by dc.state

having sum(purchase_price) = (select MAX(tp) from tmp);

 how to list second most state?

Hi Digisha,

It's easy. You can DENSE_RANK() function.

with tmpSum

as

(

select dc.State as State, SUM(purchase_price) as TotalPurchase from dwpurchases dp, dwcustomer dc where dp.customer_key = dc.customer_key and dc.country = 'Australia' group by dc.state

),tmpFinal

As

(

select  State, DENSE_RANK() over (order by TotalPurchase) from tmpSum

)select State from tmpFinal where State = 2;

I hope u understood the way of implemention.

Enjoy.

Hi folks,

For finding nth highest value  Try this for irrespective of Database , this should work

For finding nth highest value:

Select max(Column_name) from TempTable T1

where n=(Select count(distinct(Column_name) from TempTable T2 where T1.Column_name <= T2.Column_name)

Zolpidem and sleepwalking. Zolpidem.