0

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
user3809240
  • 93
  • 1
  • 3
  • 18
  • Basically another dupe of this: http://stackoverflow.com/questions/27110512/get-latest-record-for-each-id/27110597#27110597 – Marc B Nov 25 '14 at 18:29
  • try removing the `group by` line, or remove the `tp.pay_date` part of it, depending on your intent. Otherwise, you should get a different record for each pay date for each user. – DaaaahWhoosh Nov 25 '14 at 18:34

1 Answers1

2

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

Multisync
  • 8,657
  • 1
  • 16
  • 20