SQL Command to select MAX(date) & Max(time)

[Former Member]

My am having a really hard time getting a sql command to return data with the max(date) & max(time) combination. I think I need a sub query but can not figure it out. I have looked all over the internet but still don't understand it. Below is my command which returns the max date & max time but they are not from the same row of data. Please help.

SELECT "labor"."order-no", "labor"."oper-no", MAX("labor"."end-date") AS myDate, Max("labor"."end-time") AS myTime

FROM "E940LIVE"."PUB"."tm-log" "labor"

WHERE "labor"."order-no"='73153-bc' AND "labor"."company"='01'

GROUP BY "labor"."order-no", "labor"."oper-no"

[Former Member]

Wayne,

You'll actually want to contaminate the date and time together to get a true date time value. Not sure what type of database you are using so I can only guess at syntax. But you can try something like this...

SELECT 
"labor"."order-no", 
"labor"."oper-no",
MAX(CAST("labor"."end-date" + "labor"."end-time" AS DateTime)) AS myDateTime
FROM "E940LIVE"."PUB"."tm-log" "labor"
GROUP BY "labor"."order-no", "labor"."oper-no"

HTH,

Jason