Istvan Istvan - 1 year ago 52
MySQL Question

SQL Select Based On Max Value of One Column and Specific Id from Another

I am trying to return one value of a column based on the max value of another column and a specific id of a third column, all these in the same table.

I tried to do the following sql, but it does not work:

SELECT "value"
FROM event_log
JOIN (SELECT MAX("time") AS max_time
FROM event_log WHERE "eid" = 6)

The only value I want is the "value" column based on the maximum value and a specific id number of other columns of the same table.

Any suggestions?

Answer Source

You need to specify the ON clause of the JOIN operation:

SELECT "value" 
FROM event_log AS t1
   SELECT MAX("time") AS max_time 
   FROM event_log 
   WHERE "eid" = 6
) AS t2 ON t1."time" = t2.max_time
WHERE t1."eid" = 6