Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    33

    Unanswered: bring back record with earliest date

    Hi, imagine I have a large dataset of records, in which each person has 2 records for being prescribed drug A and drug B:

    Person-------Drug------Date
    1------------A---------2005/01/01
    1------------B---------2005/02/01
    2------------A---------2007/10/13
    2------------B---------2007/04/01
    . . .
    . . .
    . . .
    . . .

    How do I only bring back the records for the drug which they took first, in effect halving my dataset and getting something like:

    Person-------Drug------Date
    1------------A---------2005/01/01
    2------------B---------2007/04/01

    Eventually this is going to be used for patients who have taken up to 10 drugs, but the priciniple will remain.

    Thanks for you help!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Something like this:

    With t1 (person,earliest) as
    (select person,min(date) from MyTable)
    select * from Mytable as m inner join t1 as t on (m.person = t.person and m.date = t.earliest


    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think that those kind of issues should be solved by using OLAP specifications,
    because of it's simlicity and execution efficiency.

    AFAIK,
    OLAP specifications are supported by DB2 8.2 for LUW, DB2 9.1 for z/OS, and DB2 v5r4 for iSeries.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      MyTable(Person , Drug , Date) AS (
    VALUES
      ( 1 , 'A' , DATE('2005-01-01') )
    , ( 1 , 'B' , DATE('2005-02-01') )
    , ( 2 , 'A' , DATE('2007-10-13') )
    , ( 2 , 'B' , DATE('2007-04-01') )
    )
    SELECT Person , Drug
         , REPLACE(CHAR(Date , ISO) , '-' , '/') AS Date
     FROM (SELECT t.*
                , ROW_NUMBER()
                     OVER( PARTITION BY Person
                               ORDER BY Date
                         ) AS row_num
            FROM  MyTable AS t
          )
     WHERE row_num = 1
    ;
    ------------------------------------------------------------------------------
    
    PERSON      DRUG DATE      
    ----------- ---- ----------
              1 A    2005/01/01
              2 B    2007/04/01
    
      2 record(s) selected.
    Last edited by tonkuma; 06-09-12 at 03:35. Reason: Change REPLACE(Date , '-' , '/') to REPLACE(CHAR(Date , ISO) , '-' , '/')

Posting Permissions

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