Query for displaying second highest value from a table.

Question

Post reply

Query for displaying second highest value from a table.

Subhash-141882

Ten Centuries

Points: 1294
More actions
March 15, 2005 at 8:08 am

#65466

Hi All,
Can any one help me to write a query to display the second highest value like (Salary or Marks) from a table ?

Thanks in Advance.
Subhash

Viewing 15 posts - 1 through 15 (of 16 total)

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

This is one way to do it:

use northwind

select top 1

x.employeeId

from (

select top 2

employeeId

from employees

order by employeeId desc

) x

order by x.employeeId asc

/Kenneth

barsuk SSChampion Points: 12280 More actions · Answer 2

Or:

select max(employeeId) from employees

where employeeId <> (select max(employeeId) from employees)

Antares686 SSC Guru Points: 125444 More actions · Answer 3

The more I do these I find this to be a better method especially for large datasets. And depending on your actual future needs it could be wrapped into a stored procedure or function possibly to make easier.

Now with the cae of Salary if you are looking for the 2nd largest you will need to do a distinct and have sorted then something like this will do the trick.

DECLARE @move int

SET @move = 2

DECLARE Position_Cursor CURSOR SCROLL FOR

SELECT DISTINCT Salary FROM dbo.Employees ORDER BY Salary Desc

FOR READ ONLY

OPEN Position_Cursor

FETCH ABSOLUTE @move FROM Position_Cursor

CLOSE Position_Cursor

DEALLOCATE Position_Cursor

There are still some factors around amount of data to consider as well.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Wow A guru suggesting a cursor... that must be a first. Is the cursor more effective in the case than the set solution? Is it faster because only 1 value is being selected (vs select top 15 to 25). Wouldn't a self joined view with a rank be able to do the same thing with relatively little overhead??.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

..it's not the first, I assure you

The thing with cursors is just that they shouldn't be anyones first choice - they do however have their place, but only when a setbased solution is impossible, or proven to be too demanding.

Many times when you want just a small piece out of a very large dataset, a cursor may be effective as the 'chunking method' to cut out that slice.

For this particular case, though, I'm not too sure about the benefits. The cursor (or a plain select) still needs to sort the entire result, then pick out the 2nd from the top. Which one method that proves to be "best" I'd believe would depend on other things like hardware, tablesize, resources available and such.. In short, "it depends"

/Kenneth

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Primarily offered as another solution and should be tested as with any solution.

I have found many cases (and usually those were where sorting will produce invalid results or it was a massive dataset and I needed to go to a particular position not near the very top) that the cursor will work better especially when trying to get 1 piece of data at a specific position.

The absolute option makes jumping to the point in data very simple.

Another great thing is you can then place in a stored procedure and using a variable jump to a point you want where TOP doesn't allow the number to be defined as a variable.

But there are numerous ways to get at the data like you want.

Will you always just want the second position then go with the first method.

Sorry should have went into detail on this earlier and as said before it will "depend" on many factors, just test all solutions.

Subhash-141882 Ten Centuries Points: 1294 More actions · Answer 7

Thanks Guys,

For all your replies and answers.

I also found one more way using subQuery, but donno how effective it will be compared to the other ways.

Atlast i found different ways to find the solution from all you guys.

My query is :

select max(a.empId) 'Second Highest'

from

(

select empId from employee

where empId

< (select max(empId) from employee) and

empId > (select min(empId) from employee)

)a

Can use BETWEEN clause also i guess

Thanks once again.

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

Another just for fun but uses a variable

declare @empid int

select @empid = employeeId from

(select top 2 employeeId from employees order by employeeId desc) a

select @empid

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

steve dassin SSCrazy Points: 2396 More actions · Answer 9

You can't do things like this easily and efficiently in S2k because it lacks the sql99 functionality found in its competition (ie.Oracle/Db2).You can make up for this by using the RAC utility for S2k.Check out RACs counters (especially @rowcounters) and I'm sure you will envision an easy solution to this problem.RAC does not use cursors or UDF's to solve this class of problem.

Think of RAC as a bridge from obsolete sql92 to sql99 and beyond

http://www.rac4sql.net

Antares686 SSC Guru Points: 125444 More actions · Answer 10

Which specific functionality are you referring to?

noeld SSC Guru Points: 96590 More actions · Answer 11

If you think that the queries above are not simple how in the world the RAC4SQL package made up of a ton of code would seem simpler ?

* Noel

steve dassin SSCrazy Points: 2396 More actions · Answer 12

And just how do you know anything about the code in RAC?

noeld SSC Guru Points: 96590 More actions · Answer 13

aren't just a bunch of stored procedures?

http://www.rac4sql.net/Help/Introduction/howorks.htm

I would'n use it for things this simple

* Noel

steve dassin SSCrazy Points: 2396 More actions · Answer 14

Antares686 writes:

>Which specific functionality are you referring to?

In sql99 its referred to as analytic/windowing functions.It's what MS is trying to implement in Sql 2005 with constructs like row_number(),rank,dense_rank(),partition and order by.Post back if you want more detailed info or check out the various RAC counters