Find second highest

Question

Post reply

Find second highest

suresh_yaram

SSC Veteran

Points: 267
More actions
July 7, 2003 at 2:49 am

#82710

I have an employee table having empno , salary. How to find 2 nd highest salary thru t-sql query

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi suresh_yaram,

quote:
I have an employee table having empno , salary. How to find 2 nd highest salary thru t-sql query

what about

SELECT TOP 1 salary FROM employee WHERE salary < (SELECT MAX(salary) FROM employee) ORDER by salary DESC

??

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

suresh_yaram SSC Veteran Points: 267 More actions · Answer 2

Thanks Frank....

It did worked great. Now, I need to know Nth highest salary from the given values. Here, we may have to use recursion technique. Any clue on this?

Suresh

jpipes SSChampion Points: 12230 More actions · Answer 3

You would want to insert the salaries into a temp table that has an identity field in it, inserting the values in the order you need, then select the row based on the IDENTITY:



CREATE PROC dbo.GetNthHighestSalary (@NthHighest INT)

AS 

BEGIN

CREATE TABLE #Salary (ID INT NOT NULL IDENTITY(1,1), Salary DECIMAL(9,2) NOT NULL)

INSERT INTO #Salary (Salary)

SELECT salary FROM employee ORDER BY salary DESC

SELECT Salary FROM #Salary WHERE ID = @NthHighest

DROP TABLE #Salary

END

GO

Edited by - jpipes on 07/07/2003 06:28:46 AM

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hi Suresh,

quote:
It did worked great. Now, I need to know Nth highest salary from the given values. Here, we may have to use recursion technique. Any clue on this?

no, not yet...

A workaround could be to use a SELECT TOP xyz ....

and then catch only the needed record in your application code like:

...

your_recordset.Move <your_number>

...

But there must also be a pure SQL Server solution for this.

Cheers,

Frank

Funny things happen, when time overlaps

Edited by - a5xo3z1 on 07/07/2003 06:33:27 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

suresh_yaram SSC Veteran Points: 267 More actions · Answer 5

Thanks 'jpipes'...

It is worked for me.

Thanks Frank.. Actually, I was looking for pure SQL soln.

Regards

Suresh

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 6

Here is an example that shows the 76th highest salary. You might be able to do something like this with dynamic SQL to change the 76 to some N value.

select top 1 salary from

(select top 76 employeeId from employee

order by salary asc) a

order by salary desc

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 7

Problem similar to the paging requests of an ASP page through a recordset. Other words want records 100 to 109, then 110 to 119 or backwards 90 to 99, etc.

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 8

Greg,

What's the best way of making your code dynamic? I tried declaring a variable so that the query became:

declare @n int

set @n = 76

select top 1 salary from

(select top @n employeeId from employee

order by salary asc) a

order by salary desc

but SQL Server doesn't like it.

I can get it to work with SET ROWCOUNT @n but I'm not sure what impact this has?

Thanks

Jeremy

Melville SSCommitted Points: 1985 More actions · Answer 9

I don't think there are any problems with @@rowcount, but I would consider using min/max to get the row you want rather than top.

jpipes SSChampion Points: 12230 More actions · Answer 10

quote:
...but SQL Server doesn't like it...

I know. Annoying, isn't it, that you can't use a variable in the TOP clause...you have to use sp_executesql:



DECLARE @n INT

DECALRE @sql NVARCHAR(2000)

SET @n = 76

SET @sql = N'select top 1 salary from (select top ' + @n + ' employeeId from employee

order by salary asc) a

order by salary desc'

EXEC sp_executesql(@sql)

Cheers.

David Burrows SSC Guru Points: 65142 More actions · Answer 11

Jeremy fyi,

To use dynamic sql you have to create a variable containg the sql and use sp_execuetsql

ie

declare @n int

declare @sql nvarchar(1000)

set @n = 76

set @sql = 'select top 1 * from 

(select top '+cast(@n as varchar)+' * from employee

order by salary asc) a

order by salary desc'

sp_executesql @sql

Far away is close at hand in the images of elsewhere.
Anon.

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 12

Jeremy, hopefully jpipes and DavidBurrows answered your question. If not let me know.

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP