Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    67

    Unanswered: How to find out the oldest living person at date of death

    DB2 9.7.x LUW

    I have a view with 4 columns:
    ID integer,
    NAME varchar(200),
    DATE_OF_BIRTH date,
    DATE_OF_DEATH date

    I want to know who was the oldest living person at his/her date of death for all the people where DATE_OF_BIRTH and DATE_OF_DEATH are not null.

    ID,NAME,DATE_OF_BIRTH,DATE_OF_DEATH
    1,Test 1,2010-01-01,2012-02-03
    2,Test 2,2009-01-01,2012-02-03
    3,Test 3,2008-01-02,2013-04-06
    4,Test 4,2009-05-10,2013-04-07
    5,Test 5,2009-02-03,2013-05-08

    In this case, ID=3 will be the longest living person at his/her date of death, because he/she is older as IDs 1 and 2 were at their date of death.
    The second entry will be ID=5 because he/she will be older at ID=4's death than ID=4 itself.

    This is more a challenge than a need.

    I am waiting for some hints,

    Ferdinand Eitzen

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It looks like that you want to order persons by (DATE_OF_DEATH - DATE_OF_BIRTH) DESC.

    Is it necessary to compare with other person's DATE_OF_BIRTH or DATE_OF_DEATH?

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Are the following sequnce meet your requirements?

    Code:
    ID          NAME   DATE_OF_BIRTH DATE_OF_DEATH LIVED_YMD 
    ----------- ------ ------------- ------------- ----------
              3 Test 3 2008-01-02    2013-04-06    0005-03-04
              5 Test 5 2009-02-03    2013-05-08    0004-03-05
              4 Test 4 2009-05-10    2013-04-07    0003-10-28
              2 Test 2 2009-01-01    2012-02-03    0003-01-02
              1 Test 1 2010-01-01    2012-02-03    0002-01-02

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is untested "air code", but I'd try:
    Code:
    ; WITH cte AS (
    SELECT Row_Number() OVER (
       PARTITION BY this.id
       ORDER BY Days(candidates.date_of_death) - Days(candidates.date_of_birth) DESC) AS rn
    ,  this.*
    ,  candidates.id AS oldest_id, candidates.NAME AS oldest_name
    ,  candidates.DATE_OF_BIRTH AS oldest_date_of_birth
    ,  candidates.DATE_OF_DEATH AS oldest_date_of_death
       FROM oldest AS this
       JOIN oldest AS candidates
          ON (candidates.DATE_OF_DEATH <= this.DATE_OF_DEATH    -- Only people who have died
    	  AND candidates.DATE_OF_BIRTH <= this.DATE_OF_BIRTH)   --    and might be older
    ) SELECT *
       FROM cte
       WHERE 1 = rn
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I like Tonkuma's approach with a fetch first 1 row only, should be the cheapest method
    Dave

Posting Permissions

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