Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2010
    Posts
    1

    Unanswered: DISTINCT statement

    Hello,
    I am a relative newbie and have been having trouble with the following statement:

    SELECT DISTINCT (site), location, locationlink
    FROM Links
    GROUP BY `site`
    ORDER BY RAND()

    This does exactly what I require - takes out three fields from the 'Links' table where the 'site' field is unique. Only problem is that it pulls out the same records each time. I want it to pull out a random record from each of the records which have a particular 'site'.
    For example there may be 50 records with the site 'london' - I want a random one of those 50 records - not the same one each time.

    Can anyone help?

    cheers,
    Gary

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by floristboy View Post
    This does exactly what I require - takes out three fields from the 'Links' table where the 'site' field is unique.
    sorry, but i have a feeling you don't understand how DISTINCT works

    DISTINCT is ~not~ a function, and it applies to all columns in the SELECT clause

    you wrote it with parentheses around the first column, thinking that the "distinctness" would be applied to that column...

    SELECT DISTINCT (site), location, locationlink

    however, in actual fact the "distinctness" applies to all columns...

    SELECT DISTINCT (site), location, locationlink

    furthermore, you have a GROUP BY clause confounding the issue

    GROUP BY site ensures that there is exactly one result row per site, but since the other two columns in your SELECT clause are omitted from the GROUP BY clause (i.e. "hidden" from the GROUP BY clause), the values for those columns are indeterminate

    see MySQL :: MySQL 5.1 Reference Manual :: 11.12.3 GROUP BY and HAVING with Hidden Columns
    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
  •