CODEWITHSUNDEEP
mysqlsqlsqlite
Istvan
Istvan

Reputation: 351

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?

Upvotes: 0

Views: 809

Answers (3)

Giorgos Betsos
Giorgos Betsos

Reputation: 72195

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

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

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146557

  1. You are using a subquery, and trying to join it to an existing table, but, unless you want a full join (and you would need to explicitly specify that by including the phrase Full Join in the query if that's what you want), you need to specify join conditions for the join, which you do not.
  2. You need an alias for a subquery before you can use it in the rest of the outer query.
  3. and finally, you are attempting to select a column named value from the subquery. But there is no column named value in that subquery. Is the column value in the event_log table?

But I don't think you need a join at all. Try this:

SELECT "value",
  (Select MAX("time")
   FROM event_log WHERE "eid" = 6) maxTime
FROM event_log l

Upvotes: 0

MarkD
MarkD

Reputation: 1063

SELECT "value" 
FROM event_log AS t1
JOIN (
   SELECT MAX("time") AS max_time, eid 
   FROM event_log 
   WHERE "eid" = 6
   Group By eid
) AS t2 ON t1."time" = t2.max_tim 
AND t1.eid = t2.eid

Upvotes: 0

Related Questions