Query to select records that dont repeat.

[MichMat]

Hallo,

I would like to achive an outcome that returns only values that do not repeat in a column.

I have a database with a list of values in a column that are a conbination of driver name and destination.

SmithSydney
JonesSydney
SmithSydney
JonesMelbourne
SmithSydney
JonesMelbourne
SmithMelbourne

From the above list I would like to only extract the fact that Jones has been to Sydney only once. Actually I would like to be able to nominate the number of times if possible to identify training issues.

Like this : Select Driver, Destination FROM Jobs ....

where CombinedDriverDestination shows that the driver has only been to that destination X times (where X is a number).

I tried using Distinct but that returns all the distinct names regardless of the number of times. I suspect that count and or group by will have something to do with it however from the net tutorials it seams that this returns a single value. To be honest Im lost here and would appreciate some help.

Thank You

Michal

 

[PHV]

You want something like this ?

SELECT Driver, Destination, COUNT(*)
FROM Jobs
GROUP BY Driver, Destination

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

 

[carp]

If I'm reading your requirements correctly, you want only those rows whose values do not repeat. Try this:

SELECT Driver, Destination
FROM Jobs
GROUP BY Driver, Destination
HAVING count(*) = 1;