How to find qualifying duplicates and return only one from each group.

[LD1010]

I have a database that tracks errors (10 different error types are tracked) that have occured on different flights. The same Filght can originate from different Areas, thus flight numbers are not unique. What I need to be able to do is find all the flights for any given day where the same error has occurred two or more times for that day. Often a specific error will occured many times on the same flight on the same day. What I need to end up with is a query that that returns one row for each flight that meets the criteria.

I hope someone can give me some guidance. I'm stumped .

In the example below rows 132 and 136 do not meet the criteria because they originate from different areas and rows 143 and 144 do not meet the criteria because they do not occur on the same day. Rows 134 and 135 do meet the criteria as do rows 138 through 142. I need my query results to return only rows 134 and 138, the first instance of the duplicated error.



LogID Received Area FlightNoErrorType
131 3/2/09 9:05 A 2122 E01
132 3/2/09 13:05 A 2645 E10
133 3/2/09 8:35 A 2170 E01
134 3/2/09 10:35 A 2172 E01
135 3/2/09 14:55 A 2172 E01
136 3/2/09 18:05 B 2645 E10
137 3/2/09 13:55 B 2354 E04
138 3/2/09 10:15 B 2589 E06
139 3/2/09 8:05 B 2589 E06
140 3/2/09 15:45 B 2589 E06
141 3/2/09 10:25 B 2589 E06
142 3/2/09 10:15 C 2541 E04
143 3/2/09 10:25 C 2450 E04
144 3/3/09 8:35 C 2450 E04

 

[PHV]

What about this ?

SELECT Min(LogID) AS FirstID, Min(Received) AS FirstInstance, Area, FlightNo, ErrorType, Count(*) AS NumberOfDups
FROM yourTable
GROUP BY Int(Received), Area, FlightNo, ErrorType
HAVING Count(*) > 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LD1010]

Thank you PHV!! That's a load off of my mind. I really appreciate the help.