Finding list of unique items...

  • Thread starter Thread starter AlexT
  • Start date Start date
A

AlexT

Folks,

I'm pretty sure it's a trivial question... but bear with me anyway.

:-)

I'd like to create a query that returns every unique ID in a table,
i.e. a list of IDs that are present *exactly once* in the table
(that's not a list of DISTINCT ID, which return all IDs once...)

So assuming the following data

MyID
--------
10000
10001
10000
10005
10002
10001
10003
10005

The result of the query would be

MyID
--------
10002
10003

Any pointer / help appreciated

Best regards

--alexT
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps:

SELECT MyID
FROM table_name
GROUP BY MyID
HAVING Count(*) = 1

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQeAsKoechKqOuFEgEQIRJACfQTBqCe/5gsn/6XwFjF2DeFUWMFwAn3n6
lgYSayQF7wchdMeNg8nkfLWp
=rMUL
-----END PGP SIGNATURE-----
 
I'd like to create a query that returns every unique ID in a table,
i.e. a list of IDs that are present *exactly once* in the table
(that's not a list of DISTINCT ID, which return all IDs once...)
Click to expand...

A Totals query will do this nicely: create a Query based on your
table; select the MyID field *twice*. Change it to a Totals query by
clicking the Greek Sigma icon (like a sideways W). Leave the default
Group By on one instance of MyID, and change it to Count on the other;
put a criterion on the Count field of

=1

THe SQL would be

SELECT MyID, Count(MyID) As CountOfMyID
FROM Mytable
GROUP BY MyID
HAVING Count(MyID) = 1;


John W. Vinson[MVP]
 
Back
Top