Tricky select

[olaamen]

MySQL server version: 5.1.41

CREATE TABLE `contacto` (
`id_contacto` int(11) NOT NULL AUTO_INCREMENT,
`id_entidade` int(11) NOT NULL,
`id_operador` int(11) NOT NULL,
`data_inicio` timestamp NULL DEFAULT NULL,
`data_fim` timestamp NULL DEFAULT NULL,
`notas` varchar(400) DEFAULT NULL,
`estado` int(11) DEFAULT NULL,
`data_novo_contacto` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id_contacto`),
KEY `id_entidade` (`id_entidade`,`id_operador`),
KEY `id_operador` (`id_operador`),
CONSTRAINT `contacto_ibfk_3` FOREIGN KEY (`id_operador`) REFERENCES `operador` (`id_operador`) ON DELETE CASCADE,
CONSTRAINT `contacto_ibfk_4` FOREIGN KEY (`id_entidade`) REFERENCES `entidade` (`id_entidade`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1

 

 

The select has to show the contacts with:

-estado 0 or 2

-only one contacto by entidade (the last)

 

Something like this:

select distinct id_entidade
from contacto
where contacto.estado='0'
or contacto.estado='2'
order by data_novo_contacto

 

This almost does the trick but I need all the information of the contact.

How I do that ?

 

[Mchl]

SELECT 
  *
FROM
  contacto
WHERE
  contacto.estado IN ('0','2')
ORDER BY 
  data_novo_contacto DESC LIMIT 1

?

[riwan]

I don't think the above code will solve it, it will always return only one row (first row)

[Mchl]

I thought that's what you were after

 

How about:

 
SELECT c.* 
FROM contacto AS c
CROSS JOIN (
  SELECT DISTINCT id_entidade
   FROM contacto
   WHERE contacto.estado IN ('0','2')
) AS sq
USING(id_entidade)
ORDER BY c.data_novo_contacto

 

[riwan]

I still don't think thats what he's after.

It would be sth like this I guess

select * from contacto as c cross join 
(
SELECT max( id_contacto ) as id_contacto
FROM contacto
WHERE contacto.estado
IN (
'0', '2'
)
GROUP BY id_entidade
) as sq using (id_contacto) order by c.data_novo_contacto

The point is in the result id_entidade is unique (so if there're 2 rows return then the 2 rows has different id_entidade), but all other fields must get the latest one that satisfy the where clause.

For example

id_contacto  | id_entidade | estado

---------------------------------------------------

      1          |      2            |    0

      2          |      3            |    2

      3          |      2            |    2

 

The result would be :

 

id_contacto  | id_entidade | estado

---------------------------------------------------

      2          |      3            |    2

      3          |      2            |    2

 

The first row is not return because the latest one for id_entidade '2' is id_contacto '3'