Query that selects last line of each device?

Question

I have 3 tables in my DB:

CREATE TABLE IF NOT EXISTS `local` (
  `codigo` int(11) NOT NULL AUTO_INCREMENT,
  `IMEI` varchar(15) NOT NULL,
  `latitude` decimal(10,6) NOT NULL,
  `longitude` decimal(10,6) NOT NULL,
  `datetime` datetime NOT NULL,
  PRIMARY KEY (`codigo`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2323 ;

CREATE TABLE IF NOT EXISTS `dispositivos` (
  `codigo` int(11) NOT NULL AUTO_INCREMENT,
  `IMEI` varchar(15) NOT NULL,
  `client` int(11) NOT NULL,
  PRIMARY KEY (`codigo`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

CREATE TABLE IF NOT EXISTS `clientes` (
  `codigo` int(11) NOT NULL AUTO_INCREMENT,
  `numero_cliente` int(11) NOT NULL,
  `nome` varchar(50) NOT NULL,
  `login` varchar(20) NOT NULL,
  `password` varchar(20) NOT NULL,
  PRIMARY KEY (`codigo`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Customers have devices that send messages from their location (with the date of the device itself, so I don’t sort the table by date).

All tables are ordered by the field auto_increment.

I’d like to know how to get the last row of the table local from each device to a given client.

For example:

Client 1000 has the devices:

• 111222333444555
• 222333444555666
• 333444555666777

I want to take the last line of 111222333444555, plus the last line of 222333444555666 and the last line of 333444555666777.

Answer 1

I could not test the code, I did "in my head". See if you do what you want:

select l.*
from
(
select d.imei, max(l.codigo) cod_local
from clientes c
join dispositivos d on d.client = c.codigo
join local l on l.imei = d.imei
where c.codigo = :param_cod_cli
group by d.imei
) aux_tab
join local l on l.codigo = aux_tab.cod_local