Query to fetch row corresponding to Max value in SQL

Question

there is a table time_periods with columns

time_period    START_DATE    PAY_DATE
1          01-DEC-2014     02-DEC-2014
1          12-NOV-2014     01-DEC-2014

PEOPLE_TABLE WITH COLUMNS

TIME_PERIOD   EMP_NUM  emp_name
1               101    xyz

I have created a query :

select pt.emp_name,
max(tp.start_date),
tp.pay_date
from time_periods tp,
people_table pt
where tp.time_period=pt.time_period
group by pt.emp_name,tp.pay_date

but this is returning

emp_name    START_DATE    PAY_DATE
xyz          01-DEC-2014   02-DEC-2014
xyz          12-NOV-2014     01-DEC-2014

But this is returning two rows.... what i need is just one row which has max(start_date) and pay_date corresponding to that. that is :

emp_name    START_DATE    PAY_DATE
xyz          01-DEC-2014   02-DEC-2014

Answer 1

In Oracle you can use analytic functions for this:

select emp_name, max_start_date, pay_date
from (
    select pt.emp_name, 
           tp.start_date,
           tp.pay_date,
           rank() over(partition by pt.emp_name 
                       order by tp.start_date desc) rw,
    from time_periods tp, people_table pt
    where tp.time_period=pt.time_period
) where rw = 1;

RANK() may give you several rows for the same emp_name (if they all have the same start_date)

You can use ROW_NUMBER() instead of RANK() if you need only one row for each emp_name