7th oldest customer [message #1113] |
Tue, 02 April 2002 09:15 |
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 #1127 is a reply to message #1113] |
Wed, 03 April 2002 03:41 |
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
|
|
|