Top 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

SQL Server 2000 Forums

SQL Server Development (2000)

Top Value

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-24 : 16:26:13

writes "Hi there,

how do v find out the second highest in a table

eg:

if i have community_id's like

1900
1901
1903
1905

i want 1903 to b displayed

i know using Top 2 i can get the first 2 hightest, but i want only the second 1
thanx"

smgirard
Starting Member

2 Posts

Posted - 2001-11-27 : 15:12:16

You can also use the following:

SELECT min(s.community_id)
FROM (
SELECT top 2 community_id
FROM t
ORDER BY community_id desc
) as s

This gives you the avantage of choosing another "highest" value (i.e. you just have to change the "top" value to have the third, fourth or any other highest value)

Sylvain-Marc

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-11-27 : 18:30:08

Whatever you do, don't use this!


select community_id from t t1
where (select count(*) from t t2 where t2.community_id > t1.community_id) = 1

It will probably generate a query plan with quadratic running time. Looks ok when there's only 830 rows like Northwind.dbo.Orders, but try it on something with 100000 and you'll be waiting a long time

Nazim
A custom title

1408 Posts

Posted - 2001-11-28 : 01:03:52

Read a article by graz on sqlteam.com
Read this Article
http://www.sqlteam.com/item.asp?ItemID=566

-------------------------

Da_Retina
Posting Yak Master

109 Posts

Posted - 2001-11-28 : 06:29:00

U can do this..

SELECT TOP 1 C.community_id FROM (SELECT TOP 2 community_id FROM yourtable ORDER BY community_id asc)AS C ORDER BY salevalue DESC

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-11-28 : 07:33:52

You mean


SELECT TOP 1 C.community_id FROM (
  SELECT TOP 2 community_id FROM yourtable ORDER BY community_id DESC) AS C
ORDER BY community_id ASC

"Can't tell his ASC from his DESC"

Edited by - Arnold Fribble on 11/28/2001 07:35:25

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources