how to get second highest in a table

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)

how to get second highest in a table

Author

Topic

dasu
Posting Yak Master

104 Posts

Posted - 2004-08-31 : 08:19:46

i want solution for second max in given table
test(id int)
id
3
4
1
please suggest me proper solutions

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-31 : 08:26:35

Select min(id) From (Select top 2 id From test Order By id desc) as A

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-31 : 08:29:55

here are 2 ways

1.
declare @n int
set @n=2 -- n-th highest value
Select *
From MyTable t1
Where (@n-1) = (Select Count(Distinct(t2.id)) From MyTable t2 Where t2.id > t1.id)

2.
select top 1 id
from (select top 2 id
from MyTable
order by desc
) t
order by id

Go with the flow & have fun! Else fight the flow :)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-31 : 09:58:41

quote:

select top 1 id
from (select top 2 id 
from MyTable
order by desc
) t
order by id]

SELECT MAX(id)
FROM ( SELECT TOP 2 id FROM MyTable ORDER BY id ) top2table

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-31 : 10:06:26

it always seems so great to me that there are so many ways to do the same thing....

Go with the flow & have fun! Else fight the flow :)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-31 : 10:06:26

oops, sorry for the double posting seventhnight !
Besides mine was wrong, since it returns the second lowest

So I have to provide another one..

SELECT MAX(id) FROM #t
WHERE id < ( SELECT MAX(id) FROM #t )

This does resolve ties. (i.e will return second highest value, regardless of ties).

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-31 : 10:09:36

quote:
Originally posted by spirit1

it always seems so great to me that there are so many ways to do the same thing....

Yeah, spirit, I feel like a nagging old hag now...*s*

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources