To find n th Maximum 1

[MadhanaGopal]

In a following Table ,

Table : order
Fields: order Id
value
i want to find the 'n' th maximum value of order using a single query.
Thanx in Advance.

P.Madhana Gopal,
Principal Software Engineer,
LCube Innovative Solns. Pvt. Ltd.,
India.

 

[tlbroadbent]

The following query selects the 50th largest value.

Select Min([Value])
From
(Select Top 50 Value
From [Order] Order By [Value] desc) q Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[MadhanaGopal]

Hi Terry,
your solution is used to acheived my Goal.But is there any possibility to write it in ANSI Standard?B'use Top is SQL Server Specific. P.Madhana Gopal,
Principal Software Engineer,
LCube Innovative Solns. Pvt. Ltd.,
India.

 

[KrisSimonis]

That would be rather difficult, though TOP is a very usefull
statement to use in queries, several large database packages implement diffferent variations on it.
( for instance, under Oracle you can achieve the same effect using ROWNUM )
If you want to use a query like this on a different database platform, you probably have to rewrite it for each platform. ( and yes, that is going to give a headache,
I'm currently working on building several java query translators that make sense of object oriented query trees and turn them into SQL statements for the platform they are written for. )


Kris Simonis
OGD Software


Kris Simonis
Topdesk Information Systems