second highest figure - 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

SQL Server 2000 Forums

Transact-SQL (2000)

second highest figure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-05-30 : 08:47:45

raj writes "select second hightest salary from the table"

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-30 : 08:48:47

OK

What next ?

Damian
Ita erat quando hic adveni.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-05-30 : 09:50:42

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49355

Madhivanan

Failing to plan is Planning to fail

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-05-31 : 02:02:13

Hi raj ,you may use following script to find the same.
hirange is and alias to salary column and roysched is an alias for
employee.

select min(hirange)from
(select distinct top 2 hirange from roysched order by 1 desc)t

If you are creaing an application then you can create following SP for the same .
CREATE procedure highest
(
@no int
)
as
declare @table table (cntr int identity(1,1),
sal int )
insert into @table
select distinct hirange from roysched order by hirange desc
select sal from @table
where cntr=@no
GO
Thanks,

Vivek

Thanks,
Vivek

meenu_sow
Starting Member

5 Posts

Posted - 2005-05-31 : 06:03:58

Select top 1 colname from tablename where colname in

(Select top 4 colname from tablename order by colname desc)

order by colname asc

Meenakshi

meenu_sow
Starting Member

5 Posts

Posted - 2005-05-31 : 06:04:50

select *

From employee a

Where

(Select Count(*)

From employee b

Where b.salary >= a.salary) = (n)

Meenakshi

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-06-01 : 02:57:07

Meenakshi ,
this code "Select top 1 colname from tablename where colname in

(Select top 4 colname from tablename order by colname desc)

order by colname asc" will return the highest from colname ???
This wont work

Thanks,
Vivek

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-01 : 03:28:13

It will return 4th higest and not 2nd

and you are going to select top 1 and it will make no sense using order by after subquery

Madhivanan

Failing to plan is Planning to fail

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-06-01 : 07:44:04

It will return the highest of the columnname

Thanks,
Vivek

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources