retrieving max(date) and corresponding value in a query

db2user24 · #1 (**permalink**) 12-21-10, 13:34

Hi,

I have a table with these columns ---

ID READINGVALUE DATEADDED
----------- ------------------------ --------------------------
4328 5 2007-09-21-19.33.36.558139
4328 6 2007-10-24-17.25.32.255380
4328 7 2008-01-08-19.43.58.038657

I want to retrieve the readingvalue corresponding to MAX(DATEDADDED). Is this the most efficient way to retrieve it --

db2 "select * from table1 where id=4328 and DATEADDED=( SELECT MAX(DATEADDED) FROM table1 where id=4328)"

ID READINGVALUE DATEADDED
----------- ------------------------ --------------------------
4328 7 2008-01-08-19.43.58.038657

Lenny77 · #2 (**permalink**) 12-21-10, 15:18

Most effective this one, which I am using for 3 years, already.

I called this operation as "Linear Maximum":

Code:

SELECT 
      INT(SUBSTR(MAX(CHAR(DATEADDED) || CHAR(READINGVALUE)),27))
from table1 where id=4328

Lenny

Internet Services

Web Development

Digital Marketing

Consumer Tech