Hi,
I'm using MySQL 4.0.1 for Linux/Windows... I need to perform a query like this:
SELECT AVALIACAO, COUNT(AVALIACAO)
FROM AUDITORIAS
WHERE IDVERSAO = (SELECT MAX(IDVERSAO) FROM VERSOES WHERE DATA = '2003-08-23')
GROUP BY AVALIACAO
And its doesn't work in this version...
There is other way to do that ???
Thanks,
Oscar
PS. Above the sample script to create the tables needed to run this example query.
DROP TABLE VERSOES;
CREATE TABLE VERSOES
(
IDVERSAO CHAR(4),
DATA DATE
)
;
DROP TABLE AUDITORIAS;
CREATE TABLE AUDITORIAS
(
PROGRAMA CHAR(10),
AVALIACAO CHAR(2),
IDVERSAO CHAR(4)
)
;
INSERT INTO VERSOES (IDVERSAO, DATA) VALUES ('V001','2003-08-22');
INSERT INTO VERSOES (IDVERSAO, DATA) VALUES ('V002','2003-08-23');
INSERT INTO VERSOES (IDVERSAO, DATA) VALUES ('V003','2003-08-23');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG1','ER','V001');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG2','OK','V001');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG3','OK','V001');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG4','OK','V001');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG5','OK','V001');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG6','OK','V001');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG1','ER','V002');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG2','ER','V002');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG3','OK','V002');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG4','OK','V002');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG5','OK','V002');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG6','OK','V002');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG1','ER','V003');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG2','ER','V003');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG3','ER','V003');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG4','OK','V003');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG5','OK','V003');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG6','OK','V003');
