-
last_updated_date column
Hi,
I have a table with the following columns
NAME----------SAL------------LAST_UPDATE_DATE
ABC-----------500------------18-MAR-04
ABC-----------550------------20-MAR-04
ABC-----------700------------22-APR-04
XYZ-----------900------------15-JAN-04
XYZ-----------980------------22-FEB-04
out of the above data into this table, I need to query only most recent updated record based on the last_update_date.
How I can accomplish the query so I can get the following result
Query result
NAME----------SAL------------LAST_UPDATE_DATE
ABC-----------700------------22-APR-04
XYZ-----------980------------22-FEB-04
Thanks in advance.
-
Code:
select name, sal, last_update_date from
(select name,
sal,
last_update_date,
row_number() over (partition by name
order by last_update_date desc) as rn
from my_table
)
where rn = 1;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Or you can try this, probably the same
SELECT *
FROM
(SELECT NAME,
SAL,
LAST_UPDATED_DATE,
MAX(LAST_UPDATED_DATE) OVER(PARTITION BY NAME, SAL) MAX_DATE
FROM
MY_TABLE)
WHERE MAX_DATE = LAST_UPDATE_DATE
-
Originally posted by Highlander
Or you can try this, probably the same
SELECT *
FROM
(SELECT NAME,
SAL,
LAST_UPDATED_DATE,
MAX(LAST_UPDATED_DATE) OVER(PARTITION BY NAME, SAL) MAX_DATE
FROM
MY_TABLE)
WHERE MAX_DATE = LAST_UPDATE_DATE
I like this one better .. i think it's more maintainable.
Theoretically, it ought to be faster as well, since
Code:
MAX(LAST_UPDATED_DATE) OVER(PARTITION BY NAME, SAL)
ought to involve less work than
Code:
row_number() over (partition by name order by last_update_date desc)
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
|