Second Largest Element

Question

Post reply

Second Largest Element

Sunil Thaha

SSC Rookie

Points: 33
More actions
April 18, 2005 at 11:42 pm

#85801

How do we write a query that returns the Second Largest element in the Table

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Select max(element) AS SecondLargest FROM

Table WHERE Element!=(SELECT max(element) FROM Table)

Unfortunatly not adaptable to give the third largest, etc. I'm sure someone around here has a more adaptable solution.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sunil Thaha SSC Rookie Points: 33 More actions · Answer 2

Thank U

That just Works Fine

But just as you said it can't be scaled to find the n'th Largest Element

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Actually, come to think of it, it can.

SELECT MAX(Element) FROM Table WHERE Element NOT IN (SELECT TOP 1 Element FROM table ORDER BY Element DESC)

Replace TOP 1 with whatever you want to find other elements.

btw, this won't run very fast on larger tables, esp if the field Element is not indexed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 4

Check this

http://www.sqlservercentral.com/columnists/dasanka/findminmaxvaluesinaset.asp

in the next version o fsql server this can be done much easily

My Blog: http://dineshasanka.spaces.live.com/

Sunil Thaha SSC Rookie Points: 33 More actions · Answer 5

Sunil Thaha

SSC Rookie

Points: 33

April 19, 2005 at 3:59 am

#553377

I am using Postgres Sql in Linux.

So ...

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 6

oops!!

sorry I didn't know

My Blog: http://dineshasanka.spaces.live.com/

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 7

Maybe you should be asking at a Postgres forum instead then? Not that we do not want to help with non-SQL Server questions, but it is quite possible that you would get much better answers from people who are experts at Postgres.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Sunil Thaha SSC Rookie Points: 33 More actions · Answer 8

No NO

You please reply, It's OK .

I can convert the Queries to Postgres after all its all SQL99

gbn SSCertifiable Points: 6429 More actions · Answer 9

In T-SQL without "NOT IN"

SELECT TOP 1 *

FROM (SELECT TOP 2 * FROM Table ORDER BY MinMaxCol DESC) DR

ORDER BY MinMaxCol ASC

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

I might be wrong, but I think you're after this

USE NORTHWIND

SELECT

OD1.UnitPrice

FROM

[Order Details] AS OD1

INNER JOIN

[Order Details] AS OD2

ON

OD1.UnitPrice <= OD2.UnitPrice

GROUP BY

OD1.UnitPrice

HAVING COUNT(DISTINCT OD2.UnitPrice) = 2

UnitPrice

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

210.8000

(1 row(s) affected)

This should also work in PostGresSQL as it is pretty much ANSI SQL, though I also think you'd be better off on their mailing lists.

You only need to change 2 to whatever you like to get the n-th highest value.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]