SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Distinct & Group -- kiss my butt

    table looks like this (old table schema, bad I know):
    company
    ---------------
    Honda
    Toyota
    Chevy
    Honda
    Honda

    SELECT DISTINCT company FROM `cars`
    Returns all results

    SELECT company FROM `cars` GROUP BY company
    Returns all results

    Why cant I get this to output the distinct results?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    because either you're doing something else wrong in the query, or else your data isn't actually what you think it is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    because either you're doing something else wrong in the query, or else your data isn't actually what you think it is
    MySQL v5.1.58
    PHP v5.3.6

  4. #4
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well the 'company' column is a text column not varchar

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    there's your mistake right there

    you seriously expect company names to go up 65K bytes?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And if so, you think MySQL should look through all 65k bytes and group the duplicates together? If you need to group fulltext columns, you might be better off moving those to a separate table with a surrogate key.

    In fact, in either case, I might look into that. Depending on how you use the data, it may be faster and easier to manage companies as their own table (what are you going to do if a company changes their name or you want to add more data about the company such as web address or phone number?).
    MySQL v5.1.58
    PHP v5.3.6

  7. #7
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes, this is an older table, where I made the mistake of having duplicate rows.

    the new table has a comapny table, details table, etc. Im was just trying to perform a count of all distinct companies and was hoping it wouldnt be easy, and I was right.


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
  •