Distinct keyword - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Distinct keyword

Author

Topic

subashinikumar
Starting Member

10 Posts

Posted - 2009-07-24 : 01:44:45

I want to return the second max salary but using my code below.

but since there can be many same salary my code is wrong.
is there any possibility to use distinct keyword in my code

with cte
as
(select row_number() over (order by salary desc) as row ,empid from emp)
select * from cte where row=2;

Subashini Kumar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-24 : 01:49:24

ROW_NUMBER() will not produce duplicate values.
But if you want to display duplicate records, try this

;with cte
as (
	select	dense_rank() over (order by salary desc) as row,
		empid
	from	emp
)

select	*
from	cte
where	row = 2

N 56°04'39.26"
E 12°55'05.63"

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-24 : 02:26:36

Hi peso

we can use like this also right(as you said..)

SELECT *
FROM (
SELECT DENSE_RANK() OVER (ORDER BY SALARY DESC) AS ROWID,
EMPID
FROM TABLE_NAME
) AS D
WHERE ROWID= 2

-------------------------
R..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-24 : 13:17:43

quote:
Originally posted by rajdaksha

Hi peso

we can use like this also right(as you said..)

SELECT *
FROM (
SELECT DENSE_RANK() OVER (ORDER BY SALARY DESC) AS ROWID,
EMPID
FROM TABLE_NAME
) AS D
WHERE ROWID= 2

-------------------------
R..

this will give you all occurances from top if thats what you want where salary is second

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-25 : 03:16:42

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources