TazMayhem TazMayhem - 3 months ago 6
MySQL Question

Getting the earliest entry of a duplicate

I'm struggling with basic SQL. What I have is the following TICKET table


-----------------------
| TICKET |
-----------------------
|id | date | team |
-----------------------
| 1 |22/07/2016| A |
| 2 |24/07/2016| B |
| 3 |24/07/2016| B |
| 1 |23/07/2016| C |
| 4 |25/07/2016| A |
| 5 |26/07/2016| A |
| 5 |27/07/2016| B |
-----------------------


Sometimes, after a day or two, tickets can be affected to another team (see exemple above, ticket 1 swithes from team A to team C). What I need is a list of all tickets that were originaly meant to team A (i.e. - same id, but different team ; team A being the earliest)

I am not so sure about what I already have for the "switched team part" :

SELECT id, date, team
FROM TICKET
WHERE (
id IN (
SELECT id
FROM TICKET
WHERE (team = 'B' OR team = 'C')
)
)
AND team = 'A'


Should I put an ORDER BY date as well as a GROUP BY clause somewhere? Isn't there an better solution?

Answer
select distinct t.id
from ticket t
     inner join ticket t1 on t.id=t1.id and t1.team<>'A' and t.date<t1.date
where
    t.team='A'