find unique rows

R

reversetype

i have the following data:

ticket # CY classification
12345 40 ppdr
12345 46 ppdr
23456 40 ppdr
23456 40 ROW
37163 56 ROW
37163 56 ROW


I am having trouble realizing a query that finds duplicate ticket #'s
that either the CY or classification are different and hides ticket #'s
that have the same CY AND classification.

any help is greatly appreciated!
 
J

John Spencer

This could get tricky. Do you have instances of data like the following?

ticket # CY classification
12345 40 ppdr
12345 46 ppdr
12345 46 ppdr << note that matches the
row above>>
23456 40 ppdr
23456 40 ROW
37163 56 ROW
37163 56 ROW
47121 55 ROW << Note this is the only row
with 47121>>


This should work with the data you provided, but it will fail in some cases.
If you add one more row to the above set
12345 40 ppdr
Then Ticket 12345 will not be returned by the query below

SELECT [Ticket #], CY, Classification
FROM the Table as TB
WHERE TB.[Ticket #] in
(SELECT [Ticket #]
FROM the Table as TA
WHERE [Ticket #] in (
SELECT [Ticket #]
FROM TheTable
HAVING Count(Ticket #]) > 1)
GROUP BY [Ticket #], CY, Classification
HAVING COUNT(*) = 1)
 
M

Michel Walsh

Hi,



Another alternative:


SELECT a.ticketNumber, a.cy, a.classification

FROM mytable As a INNER JOIN myTable As b
ON a.cy=b.cy AND a.classification = b.classification

GROUP BY a.ticketNumber, a.cy, a.classification

HAVING a.ticketNumber= MIN(b.ticketNumber)



It hides all dup, cy + classification, except the one with the minimum
ticket number.


Hoping it may help,
Vanderghast, Access MVP
 
Top