Obtaining max() of multiple IDs in single query-CodePudding

I am trying to obtain a max() output of multiple IDs in the same query, joined from three different tables. id.id_client is unique, but it can share with other id_client a d.name. Each id_client have multiple time_start but I need the max() result from it separated by id_client. I am trying this but with no success:

select ts.time_start,id.id_client,d.name
from session ts, client id, name d
where
id.id_deck=d.ID_DECk AND
ts.id_client=id.id_client AND
d.name like '%John Doe%' and
ts.time_start  = (select max(time_start) from session)

So for each id that have a name as 'John Doe' i need a row with max(time_start) output.

CodePudding user response：

What you need is a group by:

  SELECT MAX(ts.time_start), id.id_client, d.name
    FROM session ts
         INNER JOIN client ID ON ts.id_client = id.id_client
         INNER JOIN name d    ON id.id_deck   = d.ID_DECk
   WHERE d.name LIKE '%John Doe%' 
   GROUP BY id.id_client, d.name;

you can also switch to ANSI JOINS, they are easier to read and write.