Select agrupado y con fecha mayor

[Lucky_Sky]

Hola Buenas tardes, os comento el problema que lleva unos días haciendo que me devane los sesos para conseguir hacerlo funcionar (y no lo he hecho, por eso recurro a vuestra ayuda)

Tengo una tabla en la que se guardan las diferentes gestiones que se realizan con los clientes.

La estructura de la tabla es la siguiente

id_gestion
id_vendedor
id_cliente
id_inmueble
fecha
hora
tipo (el tipo de gestion que se hace con el cliente char(1))

un juego de datos de prueba podría ser este:

418 27 872 H250 2016-11-25 10:00:00 A
419 27 872 H250 2016-11-25 10:30:00 C
420 27 872 H250 2016-11-25 17:00:00 V
421 27 872 H250 2016-11-25 20:00:00 R
422 28 900 P637 2016-11-28 17:00:00 A
423 28 900 P637 2016-11-29 10:00:00 B
424 33 150 S400 2016-11-29 11:00:00 A
425 33 150 S400 2016-11-30 09:00:00 P

Los tipos que se pueden dar son
A C V O R T B (se consideran cliente abierto)
P (se considera cliente pendiente)
B X (se considera cliente cerrado)

estos datos los tengo que mostrar en una página en php con los siguientes requisitos:

- Mostrar 20 registros por Estado del cliente
- Tengo que mostrar sólo el último registro por cliente
- En funcion de como se consiedere el cliente (abierto, pendiente, cerrado) tiene que aparecer en un listado distinto.

Ejemplo (para ese juego de datos):

Clientes Abiertos:
421 27 872 H250 2016-11-25 20:00:00 R
Clientes Pendientes:
425 33 150 S400 2016-11-30 09:00:00 P
Clientes Cerrados:
423 28 900 P637 2016-11-29 10:00:00 B

Ya creo haber llegado a la conclusión de que lo que primero que tengo que hacer es que la consulta SQL me de el registro con la fecha/hora más alta por cliente y después, sacar los 20 más recientes con tipo "A C V O R T B", luego los 20 más recientes con tipo "P" y por último, de esa consulta, sacar los 20 más recientes con tipo "B X".

El problema principal que tengo, es que haciendo:

Select * from gestiones group by id_cliente order by fecha desc, hora desc

me saca la más antigua en vez de la más reciente y ya no sé si es que me equivoco en el razonamiento o en la forma de hacerlo, así que agradecería cualquier ayuda que me pudiérais brindar.

¡Gracias por leerme y de antemano por la ayuda!

[Libras]

Código SQL:

SELECT t1.* FROM clientes AS t1
LEFT JOIN (SELECT MAX(fecha) AS fecha, id_cliente, tipo FROM clientes GROUP BY id_cliente, tipo) AS t2 ON (t1.id_cliente=t2.id_cliente AND t1.fecha=t2.fecha AND t1.tipo=t2.tipo)
WHERE t1.tipo='A'
 

Algo como eso ;)

[Lucky_Sky]

Hola Libras, gracias por la respuesta. Por lo que leo en el codigo que me pones, no se tiene en cuenta la hora, ¿cómo sería mirando que el máximo sea de los dos campos (fecha/hora)?

Gracias de nuevo!

Acabo de probar la sentencia SQL que me escribes, como el tipo puede ser uno de varios he probado así:

SELECT t1.* FROM gestiones AS t1
LEFT JOIN (SELECT MAX(fecha) AS fecha, id_cliente, tipo_gestion FROM gestiones GROUP BY id_cliente, tipo_gestion) AS t2 ON (t1.id_cliente=t2.id_cliente AND t1.fecha=t2.fecha AND t1.tipo_gestion=t2.tipo_gestion)
WHERE (t1.tipo_gestion='A' or t1.tipo_gestion='C' or t1.tipo_gestion='V' or t1.tipo_gestion='O' or t1.tipo_gestion='R' or t1.tipo_gestion='T' or t1.tipo_gestion='B')

lo que me lleva a que me muestre más de un registro por cliente :_(

[Libras]

Fecha y hora separados?

para eso podemos hacer algo como esto:

Código SQL:

SELECT tabla1.* FROM clientes AS tabla1
LEFT JOIN(
SELECT t1.fecha,t1.id, t2.hora FROM(
SELECT MAX(fecha) AS fecha, id FROM clientes GROUP BY id
) AS t1 
LEFT JOIN (SELECT MAX(hora) AS hora, id, fecha FROM clientes GROUP BY id,fecha) AS t2 ON (t1.fecha=t2.fecha AND t1.id=t2.id)
 
) AS tabla2 ON (tabla1.fecha=tabla2.fecha AND tabla1.hora=tabla2.hora AND tabla1.id=tabla2.id)
WHERE tabla2.id IS NOT NULL AND tipo='R'
 

Aqui nada mas regresaria un registro de tipo R

[Lucky_Sky]

Gracias de nuevo Libras, esta vez creo que sí!

He hecho unos pequeños retoques para que me muestre más tipos y para que me las ordene por fecha/hora para poder mostrar las 20 más recientes y me ha quedado algo así:

SELECT tabla1.* FROM gestiones AS tabla1
LEFT JOIN(
SELECT t1.fecha,t1.id_cliente, t2.hora FROM(
SELECT MAX(fecha) AS fecha, id_cliente FROM gestiones GROUP BY id_cliente
) AS t1
LEFT JOIN (SELECT MAX(hora) AS hora, id_cliente, fecha FROM gestiones GROUP BY id_cliente,fecha) AS t2 ON (t1.fecha=t2.fecha AND t1.id_cliente=t2.id_cliente)

) AS tabla2 ON (tabla1.fecha=tabla2.fecha AND tabla1.hora=tabla2.hora AND tabla1.id_cliente=tabla2.id_cliente)
WHERE tabla2.id_cliente IS NOT NULL AND (tipo_gestion='A' OR tipo_gestion='C' OR tipo_gestion='V' OR tipo_gestion='O' OR tipo_gestion='T' OR tipo_gestion='Q' OR tipo_gestion='R') ORDER By tabla1.fecha DESC, tabla1.hora DESC

Lo único que ahora te pregunto es si me compensa seguir trabajando así o cambiar los dos campos por uno de formato timestamp. por ahora tengo pocos registros y creo que puedo hacer fácilmente un script para editar los registros. simplificaría este cambio mucho la sentencia SQL?

Gracias!

[Libras]

Sip porque te evitarias las conversiones, y quedaria mas o menos como el primer query que te puse :)

[Lucky_Sky]

Muy Buenas de nuevo Libras, he hecho el cambio y ahora tengo un sólo campo DATETIME para la fecha, pero aunque debería funcionar, la primera consulta que me pusiste no lo hace y no entiendo por qué:

SELECT t1.* FROM gestiones AS t1
LEFT JOIN (SELECT MAX(fecha) AS fecha, id_cliente FROM gestiones GROUP BY id_cliente) AS t2 ON (t1.id_cliente=t2.id_cliente AND t1.fecha=t2.fecha)

me acaba sacando más de un registro por cliente. y por lo que creo comprobar, saca más de un registro cuando la fecha máxima aparece varias veces para ese cliente (y sólo cambia la hora) por ejemplo, para el cliente 872 que tiene cuatro gestiones con estas fechas:

2016-11-25 10:00:00
2016-11-25 10:30:00
2016-11-25 17:00:00
2016-11-25 20:00:00

Me aparecen las cuatro gestiones en la consulta en vez de sólo la última

[Libras]

Este query:

Código SQL:

SELECT MAX(fecha) AS fecha, id_cliente FROM gestiones GROUP BY id_cliente
 

Nada mas te saca un valor? o saca mas de uno? si esta sacando mas de un valor entonces hay que ver porque.

saludos