Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    62

    Unanswered: Finding the latest records using SQL

    Dear oraclan
    I'm having problems to find the max RECORDS of each age from profile table.
    I have this kind of data
    Table Profile

    ID|NAME |AGE |BIRTHDATE |
    1 | SUPIR | 20 |20-NOV-1986|
    2 | ROBERT | 20 |20-DEC-1986|
    3 | ANAK | 21 |20-DEC-1985|
    4 | ALISE | 21 |20-JAN-1985|
    5 | BUNTANG| 21|20-MAY-1985|


    Table Age

    AGE|
    ---|
    20 |
    21 |


    Target result
    2| ROBERT | 20 |20-DEC-1986|
    3 | ANAK | 21 |20-DEC-1985|

    Can anyone help me on the SQL statement.

    Tahnk very much

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Read & follow the #1 STICKY post by
    1) using CODE tags.
    2) searching this forum
    3) searching GOOGLE.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This could be one way to do it:
    Code:
    SELECT p.id, p.name, a.age, p.birthdate
    FROM T_PROFILE p, T_AGE a
    WHERE a.age = p.age
      AND p.birthdate = (SELECT MAX(p1.birthdate) 
                         FROM T_PROFILE p1
                         WHERE p1.age = p.age
                         );

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    This should also work :
    Code:
    SELECT P.Name, 
        P.Age,
        V.MaxBirthDate
    FROM
    (
        SELECT Age, 
            Max(BirthDate) MaxBirthDate
        FROM Profile
        GROUP BY Age
    ) V, Profile P
    WHERE P.BirthDate = V.MaxBirthDate;
    BTW I don't see the usefulness of storing the age, which has to be updated every day in case it is the person's birthday. I personally would only store the birthdate and use a view in which I would have the age calculated depending on the birth date and the current date. Something like this :

    Code:
    CREATE OR REPLACE VIEW V_PROFILE
    (
        Id,
        Name,
        Age,
        BirthDate
    )AS
    (
        SELECT Id,
            Name,
            (FLOOR(MONTHS_BETWEEN(Sysdate, BirthDate)/12)) Age,
            BirthDate
        FROM PROFILE
    );
    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Code:
    select id, name, age, birthdate
      from (
    select t.*,
           dense_rank( ) over( partition by age order by birthdate desc ) dr
      from t
           )
     where dr = 1

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    ah! Analytics is your friend!
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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