Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Question Unanswered: SQL - recent row

    Hi All (AIX 5.2 DB2 8.1.4)

    I have to select 10 variables from a table. 6 of these variables, together
    make up a unique identifier for a row, the sixth variable is a date.
    Often the other 5 variables will be identical across rows.

    Now my question:

    How do I select all unique rows on from the table,
    but only the rows with the recentmost date, if the rows are duplicates
    (on the 5 variables) ?

    All thougths are appreciated.
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    something like this, perhaps--
    Code:
    select pkcol1
         , pkcol2
         , pkcol3
         , pkcol4
         , pkcol5
         , datecol
      from yourtable ZZ
     where datecol =
           ( select max(datecol)
               from yourtable
              where pkcol1 = ZZ.pkcol1   
                and pkcol2 = ZZ.pkcol2   
                and pkcol3 = ZZ.pkcol3   
                and pkcol4 = ZZ.pkcol4   
                and pkcol5 = ZZ.pkcol5
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Talking Great!!

    Awesome - and simple solution

    Thanks
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  4. #4
    Join Date
    May 2003
    Location
    San Juan, PR
    Posts
    18

    Most recent value

    You can try this:

    SELECT tbl1.col1, tbl1.col2, tbl1.col3, tbl1.col4, tbl1.col5, MAX(tbl1.date1)
    FROM tbl1
    GROUP BY tbl1.col1, tbl1.col2, tbl1col3, tbl1.col4, tbl1.col5;

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aloz, that works only if col1 through col5 are the only columns you want to return in the row along with the max date

    my solution allows you to add additional columns to the SELECT list

    you cannot just add columns to your SELECT list, because then they'd also have to go into the GROUP BY, and since they would not be part of the primary key, the rows you get back could then duplicate the primary key columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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