Suzed Suzed - 3 years ago 320
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

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

The current Output

tail_number min( 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( 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 Source

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,
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 != m.minid
LEFT JOIN jobs AS j ON = j.aircraft_id
WHERE j.aircraft_id IS NULL
ORDER BY a.tail_number,

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download