second Maximum in a column

[javaranjith]

Hi
I need a help.

I would like to get a second maximum value from a colum. For eg:

Name Age
Anbu 58
Bala 85
chand 78

from the above table, maximum age is 85. I want to get 78( ie. second maximum) as well as third maximum(58).
How can we write a query.?
Help me..

[vamsi616]

Hi,

In below code replace n with the maximum value you want i.e if you want second maximum replace n with 2

Select * From tablename E1 Where
(n-1) = (Select Count(Distinct(E2.Age)) From tablename E2 Where
E2.Age> E1.Age)

Thanks

[bubbisthedog]

What RDMS are you using?

[NogDog]

In MySQL you could simply do:

SELECT * FROM table_name ORDER BY Age DESC LIMIT 2 OFFSET 1