2nd Largest or 2nd smallest element ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 Largest or 2nd smallest element ?

Author

Topic

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-12-20 : 04:13:16

Hey
Its easy to get the MAX or MIN from a table,
but what if you want to get the 2nd largest ? or 2nd smallest ?

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-20 : 04:19:59

wht do u mean by max? and min?

Vinod
Even you learn 1%, Learn it with 100% confidence.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-12-20 : 05:13:57

this sounds like schoolwork.

so rather than answering this question directly, i'll give you some hints and then you can figure things out for yourself.

Look into using the TOP operator and the ORDER BY clause. If you don't know what those are look them up in Books Online.

-ec

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 05:17:53

The version of SQL Server is important too, since it is easier with SQL Server 2005 than SQL Server 2000 to get these values.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-20 : 06:24:13

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

Madhivanan

Failing to plan is Planning to fail

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-12-20 : 06:27:25

quote:
Originally posted by sunsanvin

wht do u mean by max? and min?

Vinod
Even you learn 1%, Learn it with 100% confidence.

MAX will be the highest value of a set grouped together by a related value (or not) , for example
you could have :
[CODE]
SELECT max(code),id
from A
GROUP BY id
[/CODE]

Same for MIN but opposite.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-20 : 08:32:31

If its SQL 2005 you can do like this:-

SELECT t.Id,t.Code FROM
( SELECT ROW_NUMBER() OVER PARTITION BY Id ORDER BY Code DESC) AS 'RowNo',
   Id,
   Code
FROM Table1) t
WHERE t.RowNo=2

for second largest number for each Id. remove the DESC clause to get second smallest number.

If its 2000 you can any one method in link provided.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-20 : 09:03:34

quote:
Originally posted by pazzy11

quote:
Originally posted by sunsanvin

wht do u mean by max? and min?

Vinod
Even you learn 1%, Learn it with 100% confidence.

MAX will be the highest value of a set grouped together by a related value (or not) , for example
you could have :
[CODE]
SELECT max(code),id
from A
GROUP BY id
[/CODE]

Same for MIN but opposite.

Did you read my link?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources