Suzed Suzed - 1 month ago 6
MySQL Question

mysql Return duplicates and exclude lowest id

I am trying list all the rows from my query that will return all the duplicates next to each other so I can then grab their id's but i also want to exclude the id with the lowest number from the results. How can I go about doing that with my query.

My Query

SELECT
a.tail_number,
min(a.id),
b.aircraft_id
from aircraft a
left join jobs b on a.id = b.aircraft_id
where
a.active = 1 and b.aircraft_id is null
group by a.tail_number having count(*) > 1


The current Output

tail_number min(a.id) aircraft_id tail_count
125TH 4429 NULL 7
362FX 4223 NULL 7
439FL 4221 NULL 7
453FX 4220 NULL 7
455FX 4259 NULL 7


The output im trying to achieve

tail_number min(a.id) aircraft_id tail_count
125TH 4429 NULL 1
125TH 4430 NULL 1
125TH 4431 NULL 1
125TH 4432 NULL 1
362FX 4223 NULL 1
362FX 4224 NULL 1
362FX 4225 NULL 1
362FX 4226 NULL 1

Answer

Join with a subquery that gets the lowest ID for each tail number, and then exlude that from the results in the ON condition.

SELECT a.tail_number, a.id
FROM aircraft AS a
JOIN (SELECT tail_number, MIN(id) AS minid
      FROM aircraft
      WHERE active = 1
      GROUP BY tail_number
      HAVING COUNT(*) > 1) AS m ON a.tail_number = m.tail_number AND a.id != m.minid
LEFT JOIN jobs AS j ON a.id = j.aircraft_id
WHERE j.aircraft_id IS NULL
ORDER BY a.tail_number, a.id

I've moved the checks for active = 1 and COUNT(*) > 1 into the subquery as well, since there's no longer any grouping in the main query.