DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: last_updated_date column

  1. #1
    Join Date
    Jul 2001
    Posts
    334

    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.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  3. #3
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    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

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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)
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width