 Second from top of stack query [message #170870] |
Fri, 05 May 2006 11:20  |
crweaks23
Messages: 4
Registered: May 2006
Location: New York, NY
|
Junior Member |
|
|
Ver: Oracle 9i
I'm working with PeopleSoft - with effective dated/effective sequenced rows. I'm trying to get the second most recent row for auditing purposes, but can't seem to figure out how to get the second most recent effective dated row without excluding records that have two records sequenced on the most recent effective date.
If you're still with me, here is the "top of stack" query:
select * from PS_TABLE A
where A.EFFDT = ( SELECT MAX(EFFDT)
FROM PS_TABLE B
WHERE A.GUID = B.GUID)
and A.EFFSEQ = ( SELECT MAX(EFFSEQ)
FROM PS_TABLE D
WHERE D.GUID = A.GUID
AND D.EFFDT = A.EFFDT)
I can't figure out how to modify this to get the "second from top of stack" rows.
THANK YOU!!!
|
|
|
|
| Re: Second from top of stack query [message #170955 is a reply to message #170870] |
Sat, 06 May 2006 12:45  |
markmal
Messages: 113
Registered: April 2006
Location: Toronto, Canada
|
Senior Member |
|
|
select * from (
select guid, effdt, effseq,
DENSE_RANK() over (partition by guid order by trunc(effdt) desc) RNK
from PS_TABLE
order by guid, effdt, effseq )
where RNK <3;
verify if you need to trunc(effdt)
yes, that was for firs and second
for just a second RNK=2
[Updated on: Sat, 06 May 2006 12:47] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
