-
retrieving the 2nd most recent effdt
I am trying to retrieve data from a table that has an effdt field but I want the second highest date not the max(effdt). so for the following table
addresses
effdt
1/2/2006
3/4/2006
4/21/2006
I want to select the data in the row with the date 3/4/2006 because it is the 2nd most recent date
-
Code:
select col1 from (
select rownum rn, col1 from test_date
order by 2 desc)
where rn = 2;
Last edited by ebrian; 04-27-2006 at 02:58 PM.
-
ebrian's sql will NOT work, notice the second highest date is the 8th rownum:
Code:
SQL> SELECT HIREDATE, ROWNUM RN
2* FROM EMP ORDER BY HIREDATE DESC
dbad1>/
HIREDATE RN
----------- ----------
23-MAY-1987 11
19-APR-1987 8
23-JAN-1982 14
03-DEC-1981 12
03-DEC-1981 13
17-NOV-1981 9
28-SEP-1981 5
08-SEP-1981 10
09-JUN-1981 7
01-MAY-1981 6
02-APR-1981 4
22-FEB-1981 3
20-FEB-1981 2
17-DEC-1980 1
14 rows selected.
Use something like this instead:
Code:
SQL> SELECT * FROM (
2 SELECT JOB, HIREDATE, RANK() OVER (
3 PARTITION BY JOB
4 ORDER BY HIREDATE DESC) RN
5 FROM EMP)
6* WHERE RN = 2;
JOB HIREDATE RN
--------- ----------- ----------
ANALYST 03-DEC-1981 2
CLERK 23-JAN-1982 2
MANAGER 01-MAY-1981 2
SALESMAN 08-SEP-1981 2
Or in your case:
Code:
SELECT * FROM (
SELECT EFFDT, RANK() OVER (ORDER BY EFFDT DESC) RN
FROM EMP)
WHERE RN = 2;
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Good catch LKBrwn.
My initial query can be modified as follows to work:
Code:
select a1 from (
select a.a1, rownum rn
from (select a1 from test_date
group by a1
order by 1 desc NULLS LAST) a
where rownum <= 2)
where rn = 2;
However, if analytics were used, he may prefer to use DENSE_RANK instead of RANK, because RANK could actually skip 2 if there is a tie for 1.
Code:
SELECT * FROM (
SELECT EFFDT, DENSE_RANK() OVER (ORDER BY EFFDT DESC NULLS LAST) RN
FROM EMP)
WHERE RN = 2;
Also, if the date column can be null, notice the use of NULLS LAST !!
Last edited by ebrian; 04-27-2006 at 05:29 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|