Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2003
    Posts
    54

    Unanswered: Syntax Usage of DISTINCT ?

    I try to use the "distinct" statement with no results,
    Thanks for any help,

    SQL statement used :

    select distinct email,nom,prenom,interet,envoyer,langue,site,id from mailing_list

    It returns 2 identical records.

    I want to filter on "email"
    Records with same "email" should be returned only once.

    Where is the problem ?

    Thanks
    Pierre (Pl-Arts)

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

    Re: Syntax Usage of DISTINCT ?

    Your query is returning all distinct COMBINATIONS of (email,nom,prenom,interet,envoyer,langue,site,id )

    It cannot return 2 "identical" records, but it CAN return 2 rows with the same email. Suppose 2 people have the same email address - your query MUST show them both (or what would it show for "nom" etc.?)

    To just get the distinct email addresses:

    select distinct email from mailing_list

  3. #3
    Join Date
    Jan 2003
    Posts
    54
    So, I may not understand well the "distinct" statement.
    (I do not find any documentation)

    I want all the records, but avoid duplication of records which
    have the same email.

    So, what could be the SQL statement for that.

    I thought that,
    select distinct email,* from mailing list

    will take all records and list records with same email only once,
    whatever are the other fields content ?

    Thanks for your help,
    Pierre.
    Pierre (Pl-Arts)

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Plarde
    So, I may not understand well the "distinct" statement.
    (I do not find any documentation)

    I want all the records, but avoid duplication of records which
    have the same email.

    So, what could be the SQL statement for that.

    I thought that,
    select distinct email,* from mailing list

    will take all records and list records with same email only once,
    whatever are the other fields content ?

    Thanks for your help,
    Pierre.
    Tell me what you expect to see if the table contains the following data:

    email,nom,prenom,interet,envoyer,langue,site,id
    ---------------------------------------------------------
    abc@xyz.com, Andrews, Tony, xxx, xxx, English, London, 123
    abc@xyz.com, Larde, Pierre, yyy, yyy, Francais, Paris, 456

    Do you want to see my details, or yours? SQL cannot decide, and it will show both!

    You could decide on some arbitrary rule, like show the person with the lowest ID number:

    SELECT email,nom,prenom,interet,envoyer,langue,site,id
    FROM mailing_list m
    WHERE m.id =
    ( SELECT MIN(m2.id) FROM mailing_list m2 WHERE m2.email = m.email );

    Output:

    email,nom,prenom,interet,envoyer,langue,site,id
    ---------------------------------------------------------
    abc@xyz.com, Andrews, Tony, xxx, xxx, English, London, 123

    But this goes against what you said: "I want all the records..."

    You get all the email values, but NOT all the records.

  5. #5
    Join Date
    Jan 2003
    Posts
    54
    Not Simple apparently..

    My fields are :
    email,nom,prenom,interet,envoyer,langue,site,id

    I want all records, but if 2 or more records have the same email, I only want the one which have a value of 0 for the site .

    Thanks for your help,

    Do you know a good adress for SQL documentation ?
    (syntax, usage, examples).

    Pierre.
    Pierre (Pl-Arts)

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If 0 is the lowest site value then you could perhaps use:

    SELECT email,nom,prenom,interet,envoyer,langue,site,id
    FROM mailing_list m
    WHERE m.site =
    ( SELECT MIN(m2.site) FROM mailing_list m2 WHERE m2.email = m.email );

    Otherwise it is more tricky. Something like:

    SELECT email,nom,prenom,interet,envoyer,langue,site,id
    FROM mailing_list m
    WHERE 1 =
    ( SELECT COUNT(*) FROM mailing_list m2 WHERE m2.email = m.email );
    UNION
    SELECT email,nom,prenom,interet,envoyer,langue,site,id
    FROM mailing_list m
    WHERE site = 0
    AND 1 <
    ( SELECT COUNT(*) FROM mailing_list m2 WHERE m2.email = m.email );

    But this seems dodgy. What if there are 2 records for an email, and NEITHER has site = 0?

    I can't recommend any general SQL documentation. I can point you to Oracle's or SQL Server's on-line documentation, but they are only relevant to those databases of course.

  7. #7
    Join Date
    Jan 2003
    Posts
    54
    Thank you, I will try.

    So, "distinct" does not apply on a field, but on a
    full record. Means that all fields should be equal to make
    a distinct ?
    So if records have an incremental ID , distinct will never
    be usefull ?

    Thanks again.

    Pierre.
    Pierre (Pl-Arts)

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Plarde
    Thank you, I will try.

    So, "distinct" does not apply on a field, but on a
    full record. Means that all fields should be equal to make
    a distinct ?
    So if records have an incremental ID , distinct will never
    be usefull ?

    Thanks again.

    Pierre.
    Yes, DISTINCT works on all the fields that you select in the statement. It is only useful where the query may return duplicate RECORDS.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "So if records have an incremental ID, distinct will never be useful?"

    on the contrary, DISTINCT is quite useful, although you are probably thinking that it might not be useful if the incremental ID is one of the SELECT DISTINCT columns, and you're right

    but it is easy to imagine a table of employees, each with a unique ID, and yet still wanting to run a DISTINCT query

    for example, what countries do our employees live in?

    select distinct country from employees


    rudy

  10. #10
    Join Date
    Jan 2004
    Posts
    12

    how about something like this?

    Is there anything that might work along these lines?

    SELECT (DISTINCT Field1) as Field1, (DISTINCT Field2) as Field2

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, w0rldd0m, it doesn't work that way

    DISTINCT is not really a function, as you have coded it

    if your rows are

    1 X
    1 Y
    2 A
    2 B
    2 X
    3 A
    3 Y
    4 Z

    then what would you like to come back with?

    the distinct first column values are 1,2,3,4, and the distinct second column values are A,B,X,Y,Z

    but how exactly do you want to show them combined?

    what would your result set look like?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2004
    Posts
    12

    an example

    I need these fields:

    a. modelNumber
    b. Manufacturer
    c. LaborRate
    d. ProductDescription

    I need the records where a,b, and c are unique together
    AND
    when that condition exists append d.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if there are multiple a,b,c rows, i.e. different values of d, then which d would you like to pick for the single result row? the biggest? the smallest?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2004
    Posts
    12
    'd' should not be distinct in the select clause. so a,b, and c are distinct but d is not.

    would a union work?

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, a union isn't what you're after

    you said "a,b,c are distinct and d is not"

    okay, let's take an example

    a1 b4 c6 d12
    a1 b4 c6 d13
    a1 b4 c6 d14
    a1 b4 c6 d15
    a3 b7 c1 d20
    a3 b7 c1 d21
    a3 b7 c1 d22
    a9 b3 c7 d77


    what results do you want from the above?

    a1 b4 c6 d??
    a3 b7 c1 d??
    a9 b3 c7 d77
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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