I have a employee table with columns like emp_id, firstname, lastname, region_id, status and effective_date.

Employee Table can have multiple entries for same employee with different effective dates and statuses.

Employee can have two statuses 'Leaver' and '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

With the above data in employee table, If i want to get the latest record of james as on 1 Jan 2012, I would get record with id = 1,

If i want to get the latest record of james as on 1 May 2012, I would get record with id = 5

If i want to get the latest record of james as on 1 Aug 2012, I would get record with id = 3,

If i want to get the latest record of james as on 1 Sep 2012, I would get record with id = 6

Following query correctly gives me latest record

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

But then how do I get the other columns such as firstname , region etc.

If I put them in select clause or group by clause, I dont just get the latest record but the other records as well.

SELECT * FROM
( SELECT
    e.*,
    ROW_NUMBER() OVER (partition by emp_id order by effective_date DESC) r
FROM
    EMPLOYEE  e)
WHERE r = 1;

Above will get you a record with maximal effective__Date for every distinct emp_id.

Your second requirement of returning record for given date should be fullfiled by this query:

("status ASC" - will take care of taking "Joiner" status if there is also "Leaver" for the same date.)

 SELECT * FROM
( SELECT
    e.*,
    ROW_NUMBER() OVER (partition by emp_id order by effective_date DESC, status ASC) r
FROM
    EMPLOYEE  e
WHERE effective_date <= '<your desired date>')
WHERE r=1;

more