SQL - recent row

Tank · #1 (**permalink**) 05-03-04, 05:04

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.

r937 · #2 (**permalink**) 05-03-04, 07:05

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
       )

Tank · #3 (**permalink**) 05-03-04, 08:12

Awesome - and simple solution

Thanks

aloz · #4 (**permalink**) 05-03-04, 08:26

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;

r937 · #5 (**permalink**) 05-03-04, 09:21

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

Internet Services

Web Development

Digital Marketing

Consumer Tech