How to use a subquery to get the result I want

Question

I'm using the following query:

select distinct a.idclientecrm,
(case
when max(c.fcierre) is null then max(c.falta)
else max(c.fcierre)
end) as ultima_tarea,
d.nombre
from clientescrmporlistadeclientescrm a
inner join clientescrmporlistadeclientescrm b on a.idclientecrm = b.idclientecrm
inner join tareas c on a.idclientecrm = c.idclientecrm
inner join resultadosdetareas d on c.idresultadodetarea=d.idresultadodetarea
inner join clientescrm e on a.idclientecrm=e.idclientecrm
where b.idlistadeclientescrm = 70
and a.idlistadeclientescrm = 58
and e.fbaja is null
group by a.idclientecrm,d.nombre
limit 10

That gives me the following result.

|| *idclientecrm* || *ultima_tarea* || *nombre* ||
|| 10149991 || 2013-02-05 13:55:50 || Llamar cualquier agente ||
|| 10149991 || 2013-01-04 00:00:00 || Llamar mismo agente ||
|| 10149991 || 2013-04-23 14:47:05 || Ocupado ||
|| 10150908 || 2012-12-20 00:00:00 || Llamar cualquier agente ||
|| 10150908 || 2013-01-04 00:00:00 || Llamar mismo agente ||
|| 10150908 || 2012-11-29 00:00:00 || Venta ||
|| 10151225 || 2013-01-22 09:06:26 || Llamar cualquier agente ||
|| 10151225 || 2013-03-12 10:53:10 || Ocupado ||
|| 10151226 || 2012-07-04 00:00:00 || Llamar mismo agente ||
|| 10151226 || 2012-09-25 00:00:00 || Venta ||

I want this subquery to give me this result:

|| *idclientecrm* || *ultima_tarea* || *nombre* ||
|| 10149991 || 2013-04-23 14:47:05 || Ocupado ||
|| 10150908 || 2013-01-04 00:00:00 || Llamar mismo agente ||
|| 10151225 || 2013-03-12 10:53:10 || Ocupado ||
|| 10151226 || 2012-09-25 00:00:00 || Venta ||

I don't know If a subquery is the only way to do it or if my original query can be modified to get the result.

In case the example is not clear, I want for every idclientecrm the most recent tarea with its correspondent name. The fetch the name of the tarea from resultadosdetareas.

If any one has a similar example maybe I can adapt my query.

Thanks in advance!

Answer 1

You are looking to obtain the groupwise maximum, albeit over a combination of columns:

SELECT   c.idclientecrm,
         IFNULL(t.fcierre, t.falta) ultima_tarea,
         d.nombre

FROM     tareas c
    JOIN (
           SELECT   idclientecrm,
                    MAX(fcierre) fcierre,
                    MAX(falta  ) falta
           FROM     tareas
           GROUP BY idclientecrm
         ) t USING (idclientecrm)
    JOIN resultadosdetareas d USING (idresultadodetarea)

WHERE    IF(t.fcierre IS NULL, t.falta=c.falta, t.fcierre=c.fcierre)

GROUP BY c.idclientecrm