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

Basically another dupe of this: stackoverflow.com/questions/27110512/… — Marc B
– Marc B, Commented Nov 25, 2014 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
– DaaaahWhoosh, Commented Nov 25, 2014 at 18:34

Multisync · Accepted Answer · 2014-11-25 18:49:12Z

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

answered Nov 25, 2014 at 18:49

Multisync

8,8271 gold badge20 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Aramillo Over a year ago

check that op groups by pt.emp_name,tp.pay_date, i suggest partition by pt.emp_name,tp.pay_date

Multisync Over a year ago

@Aramillo Thanks for your comment, but "... this is returning two rows.... what i need is just one row which has max(start_date) and pay_date corresponding to that"

user3809240 Over a year ago

this query is running since a long time... no ouput

Aramillo Over a year ago

ohh, yes, i see. You're rigth then.:)

Collectives™ on Stack Overflow

Query to fetch row corresponding to Max value in SQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related