Home » SQL & PL/SQL » SQL & PL/SQL » 7th oldest customer
7th oldest customer [message #1113] Tue, 02 April 2002 09:15 Go to next message
Jo
Messages: 16
Registered: September 1999
Junior Member
Using one SQL command, how can you find out the 7th oldest customer in a table of several customers??

Any suggestionsgratefully received
Re: 7th oldest customer [message #1114 is a reply to message #1113] Tue, 02 April 2002 12:10 Go to previous messageGo to next message
Dan
Messages: 61
Registered: February 2000
Member
Try this:

select max(age), id
from tablename
having rownum=7
group by id,rownum;
Re: 7th oldest customer [message #1127 is a reply to message #1113] Wed, 03 April 2002 03:41 Go to previous message
Mike
Messages: 417
Registered: September 1998
Senior Member
Hi,

If you are using Oracle8i v8.1.6 or later I recommand you, for performance reason, to use the new ranking functions RANK() or DENSE_RANK(). The difference between RANK and DENSE_RANK is that DENSE_RANK leaves no gaps in ranking sequence when there are ties. To see the difference between the 2 have a look at the statements below and the ranking number of the employee John:

1 SELECT ename, sal,
2 DENSE_RANK() OVER (ORDER BY sal DESC) ranking
3* FROM emp

ENAME SAL RANKING
---------- ---------- ----------
KING 5000 1
SCOTT 3000 2
FORD 3000 2
JONES 2975 3
...

1 SELECT ename, sal,
2 RANK() OVER (ORDER BY sal DESC) ranking
3* FROM emp

ENAME SAL RANKING
---------- ---------- ----------
KING 5000 1
SCOTT 3000 2
FORD 3000 2
JONES 2975 4
...

The solution with this function for your problem would be somthing like:
SELECT ename, sal
FROM
(SELECT ename, sal,
RANK() OVER (ORDER BY sal DESC) ranking
FROM emp
)
WHERE ranking=7

Hope that helps
Mike
Previous Topic: tablespace locally managed
Next Topic: pro cedure
Goto Forum:
  


Current Time: Thu Mar 28 18:14:41 CDT 2024