CODEWITHSUNDEEP
mysql
Martin Ocando
Martin Ocando

Reputation: 924

selecting rows with max and min date on the same select

I have an employee table with hired date and position. For each new position they are promoted, a new record is created with hire_date as the date it starts on his new position. I'm trying to select those unique records with a minimum hired date, but the last position (max hired_date).

Table is like this:

 Id    Name    Position    hired_date
====  ======== =========== ===========
1035  Alex     Leader      1995-04-01
1035  Alex     Asst.Mgr    2015-03-21
1177  Ryan     Plan.Mgr    1996-07-23
1177  Ryan     Ops.Mgr     2014-12-17

I need to display this:

 Id    Name    Position    hired_date
====  ======== =========== ===========
1035  Alex     Asst.Mgr    1995-04-01
1177  Ryan     Ops.Mgr     1996-07-23

The query so far is like this:

select id, name, position, min(hired_date)
from employees
group by id, name
order by id, hired_date

But sometimes I get the right position, and sometimes I don't

Any ideas? Thanks for your help.

UPDATE!

This is the actual query that is displaying the error: 1242: Subquery returns more than 1 row:

SELECT cast(pernr as char) empid,
trim(vorna) fname,
case when nach2 is not null then
trim(nachn)||' '||trim(nach2) else
trim(nachn) end as lname,
(select plstx
from BI.emp_lookup el
where el.pernr = e.pernr
and not exists (select 1
                from BI.emp_lookup
                where pernr = el.pernr
                and begda > el.begda
                )
) as position,
min(begda) hired
FROM BI.emp_lookup e
where persg <> 5
group by pernr, fname, lname

Upvotes: 0

Views: 903

Answers (1)

jpw
jpw

Reputation: 44871

One way to do this (which might not be the best) is to use correlated subqueries.

select 
  id, 
  name,
  (select position 
   from employees e1 
   where e1.id = e.id 
   and not exists (select 1 
                   from employees 
                   where id = e1.id
                   and hired_date > e1.hired_date
                  )
  ) as position,
   min(hired_date) hired_date
from employees e
group by id, name;

The outer subquery gets the position for the employee for which there doesn't exists any row with a later date.

Sample SQL Fiddle

This query returns:

|   Id | Name | position |              hired_date |
|------|------|----------|-------------------------|
| 1035 | Alex | Asst.Mgr | April, 01 1995 00:00:00 |
| 1177 | Ryan |  Ops.Mgr |  July, 23 1996 00:00:00 |

In response to a comment... if your employees can have multiple positions with the same latest date you can use either of these queries to get all positions at the latest date:

select 
  e.id, 
  e.name,
  min(e.hired_date) hired_date,
  pos.position
from employees e
join (
  select id, position 
  from employees e1 
  where not exists (select 1 
                    from employees 
                    where id = e1.id
                    and hired_date > e1.hired_date
                   )
) as pos on e.id = pos.id
group by e.id, e.name, pos.position;

select 
  e.id, 
  e.name,
  min(e.hired_date) hired_date,
  group_concat(distinct pos.position) as position
from employees e
join (
  select id, position 
  from employees e1 
  where not exists (select 1 
                    from employees 
                    where id = e1.id
                    and hired_date > e1.hired_date
                   )
) as pos on e.id = pos.id
group by e.id, e.name;

If we add a position at the latest date for Alex then these queries would return an output like:

|   Id | Name |              hired_date | position |
|------|------|-------------------------|----------|
| 1035 | Alex | April, 01 1995 00:00:00 | Asst.Mgr |
| 1035 | Alex | April, 01 1995 00:00:00 |      Mgr |
| 1177 | Ryan |  July, 23 1996 00:00:00 |  Ops.Mgr |


|   Id | Name |              hired_date |     position |
|------|------|-------------------------|--------------|
| 1035 | Alex | April, 01 1995 00:00:00 | Asst.Mgr,Mgr |
| 1177 | Ryan |  July, 23 1996 00:00:00 |      Ops.Mgr |

Sample SQL Fiddle for the extra queries

Upvotes: 2

Related Questions