'How do I get all repeated ids while leaving the higher alone in firebird 2

I have the following table, I want to get all ids that have a plate that repeats, while leaving the ids that have unique plates alone and keeping the higher ids in plates that repeats.

id   plate
1    1000
2    2000
3    5000
4    5000
5    1000
6    1000
7    5000

Perform a select to get ids 3 and 1. Id 2 is not included because it is unique and 4 and 5 are higher in their repeated plates. I've tried a lot, but the limitations of Firebird 2.0 just keep me from achieving the results.

sqlfirebirdfirebird-2.0


Solution 1:[1]

In both of the below we exclude ID 2 because the plate only occurs once.

Returns the MAX ID of any plate having more than 1 ID. So this would be 4 and 5

SELECT max(ID) as maxID, plate 
FROM table 
GROUP BY plate 
HAVING count(*) > 1

Returns the Min ID of any plate having more than 1 ID. So this would be 1 and 3.

SELECT min(ID) as MinID, plate 
FROM table 
GROUP BY plate 
HAVING count(*) > 1

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 xQbert

Related Questions