Query problem, current date/time in 2 columns

[Jake5289]

I have a DB2 table set up like this...
Status Date Time
A 02/13/2002 10:02:23
A 03/02/2002 08:00:01
B 03/18/2002 13:00:01
B 05/31/2002 08:25:46
B 05/31/2002 09:31:04
And I would like a query to display each status with the most current date and time. So the final output would look like this...
A 03/02/2002 08:00:01
B 05/31/2002 09:31:04

I'm having troubles because the date and time are in seperate columns. Also, I would like to stay away from views. Thanks for the help!

 

[Shaixpeare]

Try something like this...

select status, max(char(datecol) || char(timecol)) from status_table group by status

 

[Jake5289]

That didn't work Shaixpeare. The query gave me this....
A 06/13/200108:00:01
B 06/13/200113:00:01

 

[Shaixpeare]

What part didn't work? Looks like it worked to me. I leave the formatting details to you. If you want a space or something between your date and time just add it to the concatenation.

 

[stoggers]

Hi,

A similar result to that suggested by Shaixpeare would be achieved by using the TIMESTAMP(d,t) function. However, it has the advantage of returning a value of type timestamp - therefore MAX will return the highest timestamp value as opposed to the highest value in the characterset collating sequence.

Regards,

Mike.