how to do this select?
I would like to contain the results for date, but to show the last registration of each date... but the field description is not linking correctly....
:(
# MyAdmin SQL Dump
CREATE TABLE tabela (
id int(10) unsigned NOT NULL auto_increment,
data date default NULL,
descricao varchar(20) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM AUTO_INCREMENT=8 ;
INSERT INTO tabela VALUES (1, '2005-12-20', 'error description');
INSERT INTO tabela VALUES (2, '2005-12-20', 'error description');
INSERT INTO tabela VALUES (3, '2005-12-20', 'error description');
INSERT INTO tabela VALUES (4, '2005-12-20', 'correct description 1');#the last for date
INSERT INTO tabela VALUES (5, '2005-12-24', 'error description');
INSERT INTO tabela VALUES (6, '2005-12-24', 'error description');
INSERT INTO tabela VALUES (7, '2005-12-24', 'correct description 2');#the last for date
SELECT
t1.data, MAX(t1.id) AS ultimo, t2.descricao
FROM tabela AS t1, tabela AS t2
WHERE t1.id = t2.id
GROUP BY
t1.data;
#current result (no correct)
# - '2005-12-20', '4', 'error description';
# - '2005-12-24', '7', 'error description';
how to do???
#correct result
# - '2005-12-20', '4', 'correct description 1';
# - '2005-12-24', '7', 'correct description 2';