Eu tenho uma tabela de funcionários com colunas como emp_id, firstname, lastname, region_id, status e effective_date.

A tabela de empregados pode ter várias entradas para o mesmo empregado com diferentes datas e status efetivos.

O funcionário pode ter dois status 'Leaver' e 'Joiner'.

id     emp_id    firstname     region    status     effective_date  
1       1         James        Asia      Joiner     1-Jan-2012 
2       1         James        UK        Leaver     1-Aug-2012
3       1         James        USA       Joiner     1-Aug-2012
4       1         James        Asia      Leaver     1-May-2012
5       1         James        UK        Joiner     1-May-2012
6       1         James        USA       Leaver     1-Sep-2012

Com os dados acima na tabela de funcionários, se eu quiser obter o mais recente registro de james como em 1 de janeiro de 2012, eu teria registro com id = 1,

Se eu quiser obter o registro mais recente de james em 1º de maio de 2012, eu obteria um registro com id = 5

Se eu quiser obter o registro mais recente de james em 1 de agosto de 2012, eu gravaria com id = 3,

Se eu quiser obter o mais recente registro de james como em 1 de setembro de 2012, eu iria gravar com id = 6

A consulta a seguir corretamente me fornece o registro mais recente

SELECT 
        emp_id, 
        MAX(effective_date) AS latest_effective_date
FROM 
        EMPLOYEE
GROUP BY 
        emp_id

Mas então como eu obtenho as outras colunas como nome, região etc.

Se eu colocá-los em select clause ou group by clause, eu simplesmente não obtenho o último registro, mas os outros registros também.