MySQL

help - ordering

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Oct 2009

Posts: 24

Reputation: gpss is an unknown quantity at this point

Solved Threads: 0

gpss

Offline

Newbie Poster

help - ordering

Oct 21st, 2009

Hello! I need some help please..

i searched a lot, and tried everything... can someone help me please..

here's the deal:

i have this select:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT * 
FROM localizacao, cliente, dispositivo                         
WHERE disp_id = clien_disp_id AND disp_id = local_disp_id 
ORDER BY local_id desc
SELECT * 
FROM localizacao, cliente, dispositivo                         
WHERE disp_id = clien_disp_id AND disp_id = local_disp_id 
ORDER BY local_id desc

it's output:
http://i.imagehost.org/0746/tabela.jpg

but what i really want is:

i want to select my client's, but where local_id is the last id i receive.. but i don't wanna show repetitive client's, i just want to show the last local_id for each client....
i want like to show only this ones: http://i.imagehost.org/0883/tabela2.jpg in the red circle

you know what i mean?
it's like two order by's but not showing repetitive client's... i tried distinct... but maybe im making it wrong..

help :s

thanks a lot

Last edited by gpss; Oct 21st, 2009 at 12:53 pm.

•

Join Date: Sep 2007

Posts: 21

Reputation: spthorn is an unknown quantity at this point

Solved Threads: 5

spthorn

Offline

Newbie Poster

Oct 22nd, 2009

This was a good exercise! This code will do what you want:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT #localizacao.*, #cliente.*, #dispositivo.*
FROM #dispositivo
JOIN #localizacao on #localizacao.local_disp_id = #dispositivo.disp_id
JOIN #cliente on #cliente.clien_disp_id = #dispositivo.disp_id
JOIN (
	SELECT max(local_id) as localid, clien_id, local_disp_id
	FROM #localizacao, #cliente, #dispositivo                         
	WHERE disp_id = clien_disp_id AND disp_id = local_disp_id 
	GROUP BY local_disp_id, clien_id
) as a
on a.localid=#localizacao.local_id and a.clien_id=#cliente.clien_id and a.local_disp_id=#dispositivo.disp_id
select #localizacao.*, #cliente.*, #dispositivo.*
FROM #dispositivo
join #localizacao on #localizacao.local_disp_id = #dispositivo.disp_id
join #cliente on #cliente.clien_disp_id = #dispositivo.disp_id
join (
	select max(local_id) as localid, clien_id, local_disp_id
	FROM #localizacao, #cliente, #dispositivo                         
	WHERE disp_id = clien_disp_id AND disp_id = local_disp_id 
	group by local_disp_id, clien_id
) as a
on a.localid=#localizacao.local_id and a.clien_id=#cliente.clien_id and a.local_disp_id=#dispositivo.disp_id