[ACOLLINS]

I am using SQL QueryAnalyser, and need to select from a list of people in a table with fields
name
address1
address2
county
postcode
..
..
.
but i have different people at the same address and want to select on distinct postcode.
I have used SELECT DISTINCT postcode from table but this only gives me a list of the postcodes how do i get the rest of the info, I do not care which of the duplicates i get.

Thanks

[alligatorsql.com]

Hello,

which of the datas do you want to select ?
DISTINCT will show you only the unique entries of the field - in your example the postcode.
Oracle have the same problem than you have. How will the database decide, which postcode is the correct postcode for the people ?

Or do I get something wrong ?

Hope that helps ?

Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com

[andrewst]

The problem is, 2 or more people at the same postcode are NOT duplicates, so how can the DBMS decide which person to return? If you just want one arbitrary person per postcode, then you can do this:

SELECT postcode, MIN(name)
FROM people
GROUP BY postcode;

That gets you one person per postcode (the one with the "minimum" name!) But you are still missing the address details. You can't use MIN on those fields as well, otherwise you will end up with a dog's breakfast of values from different people. So you do this:

SELECT name, address1, address2, county, postcode
FROM people
WHERE (postcode, name) IN
( SELECT postcode, MIN(name)
FROM people
GROUP BY postcode
);

[ACOLLINS]

Thanks for the reply, the first part solved the problem i had. It would be nice to get the second bit working but i get an error
Line 3: Incorrect syntax near ','.


SELECT *
FROM tablename
WHERE (ucn, pc) IN
( SELECT min(ucn), pc
FROM tablename
GROUP BY pc)

regards

[andrewst]

Seems your DBMS can't handle multiple columns in the IN clause - I know Oracle can.

How about an in-line view?

SELECT *
FROM tablename t,
( SELECT min(ucn) AS minucn, pc
FROM tablename
GROUP BY pc) v
WHERE v.minucn = t.ucn
AND v.pc = t.pc;