SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Evangelist
    Join Date
    Jan 2002
    Location
    Scotland, UK
    Posts
    530
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    [MySQL] How to select only unique entires from table?

    Hi folks,

    Probably the simplest question ever but one thats eluded answering for myself

    I have a table with say 10 records and say that the records have a location field. How would I select only records with different locations ?

    Eg:

    1 - Jim - Florida
    2 - Mark - Texas
    3 - Kim - Florida
    4 - Phil - Boston
    5 - Gary - Los Angeles
    6 - Warren - Texas
    7 - Will - Florida
    8 - Grant - Texas
    9 - Lisa - New York
    10 - Julie - New York

    So my SELECT statement would produce :

    1 - Jim - Florida
    2 - Mark - Texas
    4 - Phil - Boston
    5 - Gary - Los Angeles
    9 - Lisa - New York

    I would want the full record returned not just the location.

    Many thanks people, hope you can help

    Regards,

    Rikki

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1st of all; why?
    I mean why select Jim from FL, but not Kim?

    Anyway, "group by location" maybe?

    (And yes, grouping by location and not using an aggregate function for name will work in mysql, but not in other, normal dbs)

  3. #3
    SitePoint Evangelist compwizard's Avatar
    Join Date
    May 2002
    Location
    United States
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I believe something like this would work:
    Code:
    SELECT DISTINCT location FROM tablename
    or
    Code:
    SELECT location, count(location) FROM tablename GROUP BY location
    would give you a count of each location
    Compwizard
    "There are 10 kinds of people in this world -- those who know binary, and those who don't."

  4. #4
    SitePoint Evangelist compwizard's Avatar
    Join Date
    May 2002
    Location
    United States
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oops, on my first example, just add your other fields seperated by commas
    Compwizard
    "There are 10 kinds of people in this world -- those who know binary, and those who don't."

  5. #5
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by compwizard
    Oops, on my first example, just add your other fields seperated by commas
    Doesn't DISTINCT depend on all the selected fields?
    I.e.
    select distinct name, location ...
    will return all 10 rows, while
    select distinct location ...
    will return only 5 rows

    If there should be an 11th row with another Julie from NY, then distinct would eliminate one duplicate in the combination name+location

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,334
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, DISTINCT operates on all columns of the result set (i.e. all columns in the SELECT list)

    all databases support GROUP BY without an aggregate in the SELECT list

    "select only records with different locations" doesn't really make sense unless you specifiy how to choose which row you want amongst the multiple rows that have the same location
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist compwizard's Avatar
    Join Date
    May 2002
    Location
    United States
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oops, my mistake...well anyway, my second example should work, even if it is not exactly what was asked for.
    Compwizard
    "There are 10 kinds of people in this world -- those who know binary, and those who don't."

  8. #8
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    all databases support GROUP BY without an aggregate in the SELECT list
    ???

    select location, name
    from test
    group by location

    => "Column 'test.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

    While...
    select location, max(name)
    from test
    group by location
    ... works OK
    (not sure max(name) makes sense in any way )

    DB = Sql Server 2000
    Last edited by jofa; May 28, 2003 at 09:11.

  9. #9
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think what Rudy meant was that you don't need an aggregate in the SELECT clause for a GROUP BY to work.

    You still need to GROUP BY any non-aggregate fields in the SELECT clause, which is why your example above didn't work.

  10. #10
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, what I meant with this "...grouping by location and not using an aggregate function for name...", was that a query like
    select location, name
    from XXX
    group by location
    shouldn't work normally, but in mysql it does (!)

    And grouping by "location, name" wasn't an option, since it wouldn't return the requested result

  11. #11
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry I took it out of it's context by reading your last post. Still am not sure how that's related to your quoting Rudy though

  12. #12
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know either... I think I've had some sort of out of brain experience the whole day today

    Main point is: you should always include columns in either an aggregate function or the group by
    You can do non-standard things in mysql, and will most certainly get problems if/when moving to another type of db

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,334
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, jofa, you're right, mysql does allow you to break the rule about mentioning GROUP BY columns in the SELECT list as non-aggregates... or vice versa, actually

    bad, mysql, very bad

    to be fair, they do put a warning on it, and say that it will produce unpredictable results

    my other comment was simply that you do not need to have an aggregate in order to use a GROUP BY... but we all knew that, right?

  14. #14
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "unpredictable results"

    Now, that's something to be afraid of (compared to null )


Bookmarks

Posting Permissions

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