I am using SQL QueryAnalyser, and need to select from a list of people in a table with fields
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.
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 ?
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)
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
WHERE (postcode, name) IN
( SELECT postcode, MIN(name)
GROUP BY postcode