Second Highest

Question

Post reply

Second Highest

karunakar2351

Right there with Babe

Points: 729
More actions
September 16, 2013 at 5:27 am

#395490

Could anyone know how to write the query fro fetching the second highest salary without using top, rowcount and rank keywords?

Viewing 11 posts - 1 through 10 (of 10 total)

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

psingla Hall of Fame Points: 3840 More actions · Answer 1

hopefully following will help

http://pramodsingla.wordpress.com/category/interview-questions-2/

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

siggemannen SSCrazy Points: 2436 More actions · Answer 2

DENSE_RANK should do it 😉

Or SELECT 'me' if you're a bit on the joking side...

Or:

SELECT MAX(salary)

FROM t

WHERE salary < (SELECT MAX(salary) FROM t)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

karunakar2351 (9/16/2013)
Could anyone know how to write the query fro fetching the second highest salary without using top, rowcount and rank keywords?

Why the strange restrictions?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

psingla (9/16/2013)

hopefully following will help

http://pramodsingla.wordpress.com/category/interview-questions-2/%5B/quote%5D

siggemannen (9/16/2013)
DENSE_RANK should do it 😉

Neither of the proposed solutions meet the restrictions posed by the OP.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

psingla Hall of Fame Points: 3840 More actions · Answer 5

Jeff Moden (9/16/2013)

psingla (9/16/2013)
hopefully following will help
http://pramodsingla.wordpress.com/category/interview-questions-2/%5B/quote%5D
siggemannen (9/16/2013)
DENSE_RANK should do it 😉
Or SELECT 'me' if you're a bit on the joking side...
Or:
SELECT MAX(salary)
FROM t
WHERE salary < (SELECT MAX(salary) FROM t)
Neither of the proposed solutions meet the restrictions posed by the OP.

Jeff,I suppose the following two solution that you can find at http://pramodsingla.wordpress.com/category/interview-questions-2/ are meeting the restrictions:

declare @n smallint;

set @n =4

;with cte as (SELECT name,salary,

dense_rank() OVER( ORDER BY salary DESC) AS rowid

FROM #t1

)

SELECT *

FROM cte

WHERE rowid = @n

select distinct SALARY from #t1 as n

where (select count(distinct SALARY) from #t1 where SALARY>=n.SALARY)=4

None of these is using rank,top or rowcount

correct me if i am missing something

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 6

Hi

You can use rownumber

Select * from (

SELECT ROW_NUMBER() over(partition by columna order by columnname <desc or asc>) Get_Rows

FROM Table_Name

) m where Get_Rows = 2

I hope it helps.

Shaun

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

psingla (9/16/2013)

Jeff Moden (9/16/2013)
psingla (9/16/2013)
hopefully following will help
http://pramodsingla.wordpress.com/category/interview-questions-2/%5B/quote%5D
siggemannen (9/16/2013)
DENSE_RANK should do it 😉
Or SELECT 'me' if you're a bit on the joking side...
Or:
SELECT MAX(salary)
FROM t
WHERE salary < (SELECT MAX(salary) FROM t)
Neither of the proposed solutions meet the restrictions posed by the OP.
Jeff,I suppose the following two solution that you can find at http://pramodsingla.wordpress.com/category/interview-questions-2/ are meeting the restrictions:
declare @n smallint;
set @n =4
;with cte as (SELECT name,salary,
dense_rank() OVER( ORDER BY salary DESC) AS rowid
FROM #t1
)
SELECT *
FROM cte
WHERE rowid = @n
select distinct SALARY from #t1 as n
where (select count(distinct SALARY) from #t1 where SALARY>=n.SALARY)=4
None of these is using rank,top or rowcount
correct me if i am missing something

Technically, you're correct. DENSE_RANK wasn't precisely mentioned. But, since RANK isn't allowed, I'd have to ask the OP if DENSE_RANK is allowed. I'm thinking "not" but I could be wrong.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

siggemannen (9/16/2013)
SELECT MAX(salary) FROM t
WHERE salary < (SELECT MAX(salary) FROM t)

Apologies. I missed this. This would do it while meeting all of the expressed and implied restrictions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kigann Newbie Points: 8 More actions · Answer 9

Move to Sql Server 2012.

They have introduced OFFSET and FETCH

Example:

Select salary from table

order by salary desc

offset 1 row 'Start at 2nd row

fetch next 1 rows only 'show only the 2nd row

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 10

If you are concerned only about output then use any of these solutions (MAX or OFFSET...FETCH).

If you are also looking for performance (and your table has proper indexes and primary keys) then use (OFFSET...FETCH).