Results 1 to 5 of 5

Thread: Select Distinct

  1. #1
    Join Date
    May 2003
    Posts
    2

    Unanswered: Select Distinct

    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

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Distinct

    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

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Select Distinct

    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
    );

  4. #4
    Join Date
    May 2003
    Posts
    2
    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

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •