OraFAQ Forum: SQL & PL/SQL » Find second oldest person

Home » SQL & PL/SQL » SQL & PL/SQL » Find second oldest person

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Find second oldest person [message #10729]

Thu, 12 February 2004 09:09

Michael Hart
Messages: 4
Registered: February 2004

Junior Member

Hello, how would i be able to find the second oldest person from a table.

�To find the�oldest person I would do the following.

��SELECT name, DOB FROM�mytable
�� WHERE DOB = (SELECT MIN(DOB) FROM mytable);

�but I am not sure how to find the second oldest.

�

�

Report message to a moderator

Re: Find second oldest person [message #10730 is a reply to message #10729]

Thu, 12 February 2004 09:34

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Analytic functions to the rescue:

SELECT rnkd.name
,      rnkd.dob
FROM  (SELECT mt.name
       ,      mt.dob
       ,      DENSE_RANK() OVER (ORDER BY mt.dob) age_rank
       FROM   mytable      mt) rnkd
WHERE  rnkd.age_rank = 2

HTH,

A.

Report message to a moderator

Re: Find second oldest person [message #10865 is a reply to message #10729]

Fri, 20 February 2004 20:23

supriya
Messages: 5
Registered: February 2004

Junior Member

select surname,dob
from people
where dob = (select max(dob) from people
where dob <
(select max(dob) from people))

Report message to a moderator

Previous Topic:	Down Arrow Key
Next Topic:	Urgent Please

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 08:12:30 CDT 2024