Query to get 2nd largest value

Question

Query to get 2nd largest value

chitturiii

SSC Enthusiast

Points: 185
More actions
February 25, 2011 at 5:28 am

#234275

Hi,
Can anyone suggest me the query to get the 2nd largest value(say salary) from two tables(using any join between employee and department tables).
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

this is a fairly easy question, and it comes up a lot in homework type situations; so I'll give you a hint and information rather than a full answer:

there's two common ways. one is to use the ROW_NUMBER() function with the [Salary] field you mentioned int he PARTITION BY segment; that will generate an integer value representing each item 's order; then if you wrap that in an outer SELECT statement, you could then choose where the row_number()'s alias = 2 to get the second highest.

the second way is using a nested select featuring two different TOP statements. this is "how it was done" before row_number() came out in SQL 2005.

if you SELECT TOP 2...ORDER BY Salary, you have the value you were looking for, right?

then just wrap that same query with an additional TOP 1 ORDER BY Salary DESC, which reverses the order, and selects just the row you were after.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

In addition to what Lowell said, I wrote an article on this exact subject a while back

http://www.sqlservercentral.com/articles/T-SQL/71571/

Clear Sky SQL
My Blog[/url]

chitturiii SSC Enthusiast Points: 185 More actions · Answer 3

Thanks Lowell...

I think the query goes on as:

Select top 1 sal from employee where sal in (select top 2 sal from employee order by sal desc)

order by sal

chitturiii SSC Enthusiast Points: 185 More actions · Answer 4

Thank you Dave Ballantyne...

Your post is quite good and easily understandable...

Lowell SSC Guru Points: 323518 More actions · Answer 5

chitturiii (2/25/2011)
Thanks Lowell...
I think the query goes on as:
Select top 1 sal from employee where sal in (select top 2 sal from employee order by sal desc)
order by sal

that looks correct chitturiii ;

the row number example is structured very similarly; i believe the purpose of the exercise is to teach you to learn to wrap a query with an outer query to get/filter the results you need. That's an important concept to grasp and keep in your toolbox.

SELECT *

FROM

(

SELECT

ROW_NUMBER() OVER(PARTITION BY SAL ORDER BY SAL DESC) AS RW, --my alias for the row_numnber function

SAL

FROM EMPLOYEE

) MyAlias --alias for the sub query

WHERE RW = 2

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!