2nd Highest Value - 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

2nd Highest Value

Author

Topic

rameshabap988
Starting Member

5 Posts

Posted - 2011-05-25 : 04:37:54

Hi,

I have a table like:EMPNAME EMPSAL
AB 10,000
BC 20,000
CD 12,000
DE 14,000

My Requirement is to display the second highest salary employee name.Could you please any help me how to write sql...

Expected Out as per above table:DE
Thanks in Advance!!

Thanks&Regards
Ramesh

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-05-25 : 04:48:42

select *
from table
where row_number() over (order by EMPSAL desc) = 2

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-25 : 04:58:06

You will need to wrap that in CTE or derived table. Can't user window function directly in WHERE clause

KH
[spoiler]Time is always against us[/spoiler]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-05-25 : 05:11:51

A little too fast there :)

->

;with cte as (select *, rownum = row_number() over (order by EMPSAL desc)
  from table
  where your_where_criteria_here)
select *
from cte 
where rownum = 2

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-05-30 : 07:04:20

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

Madhivanan

Failing to plan is Planning to fail

rameshabap988
Starting Member

5 Posts

Posted - 2011-05-30 : 07:17:32

Hi,

This is in mysql...your Query is not working here.Please give me answer in SQL.

Thanks&Regards
Ramesh

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-05-30 : 07:31:25

This is a forum for MS SQL Server only...try dbforums or forum.mysql.com instead.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

rameshabap988
Starting Member

5 Posts

Posted - 2011-05-31 : 00:24:50

Hi,

Thanks for Replay....

where can i post sql related things....

Thanks&Regards
Ramesh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-31 : 00:29:56

as Lumbago suggested,

Post your question on MySQL on
http://www.dbforums.com/
or
forum.mysql.com

KH
[spoiler]Time is always against us[/spoiler]

SachinSM
Starting Member

1 Post

Posted - 2011-06-02 : 02:06:26

you can find for any position by changing the value 2:

SELECT TOP 1 NAME FROM
(SELECT DISTINCT TOP 2 * FROM EMP ORDER BY EMPSAL DESC) T
ORDER BY EMPSAL

Sachin Mutange

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources