last_updated_date column

[aph]

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.

[jmodic]

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;

[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

[slimdave]

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

MAX(LAST_UPDATED_DATE) OVER(PARTITION BY NAME, SAL)

ought to involve less work than

row_number() over (partition by name order by last_update_date desc)