-2

I have these two tables:

Person:
+----+-------+
| ID | name  |
+----+-------+
|  1 | John  |
|  2 | Frank |
+----+-------+

Position:
+----+---------+----------+------------+
| ID | name    | personID | startDate  |
+----+---------+----------+------------+
|  1 | Cashier | 1        | 2013-01-01 |
|  2 | Manager | 1        | 2013-04-23 |
|  3 | Cashier | 2        | 2014-02-01 |
+----+---------+----------+------------+

The Position table tracks various positions that a person has held.

How can I create a listing that shows each person and their current position (which would be whatever position has the latest start date)? Essentially I need to limit the JOIN of the Position table to only return one result.

I tried the following code.

SELECT p.id, h.positionID FROM person p JOIN position h ON p.id = h.personID
Gunaseelan
  • 2,137
  • 3
  • 30
  • 36
Brian
  • 6,656
  • 11
  • 41
  • 75
  • possible duplicate of [LIMITing an SQL JOIN](http://stackoverflow.com/questions/494974/limiting-an-sql-join) – James Hunt Jul 23 '14 at 12:55
  • Have you tried any code? – Gunaseelan Jul 23 '14 at 12:55
  • @JamesHunt That seems to be asking for the opposite, limiting the left side of the table. I want to limit the right side. – Brian Jul 23 '14 at 13:02
  • @Gunaseelan I don't have much beyond the normal join. I've messed around with subqueries but keep hitting a wall. `SELECT p.id, h.positionID FROM person p JOIN position h ON p.id = h.personID` – Brian Jul 23 '14 at 13:07

1 Answers1

4

Try the following :-

select p.ID, pp.name PersonName, p.name PositionName ,p.startDate 
from Position p
inner join
(select personID, max(startDate) sdate from Position group by personID) as a
on p.personID = a.personID and p.startDate = a.sdate
left join Person pp
on pp.ID = p.personID

Yet, it is highly advised that you post the code that you tried.

SQL Fiddle

KrazzyNefarious
  • 3,131
  • 3
  • 18
  • 32