(Resolvido) Selecionar tabela filtrando dados duplicados por data

[João Carlos C.]

Saudações a todos!

Estou tendo uma dificuldade para fazer um select se puderem ajudar agradeço.

para quem puder dar uma força fiz aqui uma query para criar um bd com os campos e alguns inserts assim fica mais fácil ajudar;

drop table if exists EMPRESA;
drop table if exists EMPRESA_IDENTIDADE;

/*==============================================================*/
/* Table: EMPRESA */
/*==============================================================*/
create table EMPRESA
(
EMP_COD int auto_increment,
EMP_CATEGORIA char(1) not null,
EMP_PERMISSAO char(1) not null,
EMP_DATAINSC datetime not null,
UNIQUE (EMP_COD),
primary key (EMP_COD)
);

/*==============================================================*/
/* Table: EMPRESA_IDENTIDADE */
/*==============================================================*/
create table EMPRESA_IDENTIDADE
(
EMP_I_ID int auto_increment,
EMP_COD int not null,
EMP_I_CNPJ varchar(18) not null,
EMP_I_RAZAO varchar(40) not null,
EMP_I_FANT varchar(40) not null,
EMP_I_DATAMOD datetime not null,
UNIQUE (EMP_I_ID),
primary key (EMP_I_ID)
);
INSERT INTO EMPRESA VALUES
(Null, 'F', 'C', NOW()),
(Null, 'F', 'C', NOW()),
(Null, 'F', 'C', NOW()),
(Null, 'F', 'C', NOW()),
(Null, 'F', 'C', NOW()),
(Null, 'F', 'C', NOW());

INSERT INTO EMPRESA_IDENTIDADE VALUES
(Null, 1, '00.000.000/000-00', 'nomeexemplo.ltda', 'nomefantasia', NOW()),
(Null, 2, '00.000.000/000-00', 'nomeexemplo.ltda', 'nomefantasia', NOW()),
(Null, 3, '00.000.000/000-00', 'nomeexemplo.ltda', 'nomefantasia', NOW()),
(Null, 4, '00.000.000/000-00', 'nomeexemplo.ltda', 'nomefantasia', NOW()),
(Null, 5, '00.000.000/000-00', 'nomeexemplo.ltda', 'nomefantasia', NOW()),
(Null, 6, '00.000.000/000-00', 'nomeexemplo.ltda', 'nomefantasia', NOW()),
(Null, 6, '00.000.000/000-00', 'nomeexemplo.ltda', 'nomefantasia', NOW()),
(Null, 6, '00.000.000/000-00', 'nomeexemplo.ltda', 'nomefantasia', NOW()),
(Null, 5, '00.000.000/000-00', 'nomeexemplo.ltda', 'nomefantasia', NOW());

Se criarem uma query simples para selecionar todos os campos de empresa_identidade que sejam de uma categoria e que estejam com a permissão liberada ficaria desta forma;

SELECT * FROM EMPRESA_IDENTIDADE
JOIN
EMPRESA
ON EMPRESA_IDENTIDADE.EMP_COD IN( EMPRESA.EMP_COD ) AND EMPRESA.EMP_CATEGORIA = 'F' AND EMPRESA.EMP_PERMISSAO = 'C'

Desta maneira ele me trás todos os registros repetindo de nº6 três vezes e o de nº5 duas.

Pois bem ai meu problema preciso que selecione onde em EMP_COD houver registros duplicados apenas o mais recente pela data no campo EMP_I_DATAMOD

Já tentei com subselect até agrupar e selecionar a data max(EMP_I_DATAMOD) dentro do grupo mas não consegui sucesso, se puder ajudar fico muito grato esta query esta botando abaixo toda minha estrutura de banco de dados.

preciso fazer desta maneira porque os registros não serão modificados sempre que houver alteração será criado um registro novo com a data mais recente para podermos ter um histórico de modificações nos registros.

desde já agradeço.

Editado Agosto 12, 2010 por Denis Courcy
Melhorar entendimento do código (select)

[Denis Courcy]

Oi, 'João Carlos C.'

Faça assim:

SELECT DISTINCT * 
FROM EMPRESA_IDENTIDADE ei
JOIN EMPRESA e ON ei.EMP_COD = e.EMP_COD
WHERE e.EMP_CATEGORIA = 'F' AND e.EMP_PERMISSAO = 'C' 
   AND ei.EMP_I_DATAMOD = (SELECT max(ei2.EMP_I_DATAMOD)
                           FROM EMPRESA_IDENTIDADE ei2
                           WHERE ei2.EMP_COD = e.EMP_COD)

Este código tem uma falha. Se houver datas e horas iguais para um mesmo ei2.EMP_COD ele trará duplicado, pois não saberá qual o maior.

[João Carlos C.]

Opa Denis Parabéns cara, Muito Obrigado.

Resolvido.

:)