SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can't do SELECT DISTINCT

    I'm grabbing a list of different sports events from MySQL, goes something like

    SELECT DISTINCT distance, eventname FROM events

    That gives me the exact rows I need, but how to I get the id column? If I do something like

    SELECT DISTINCT distance, eventname, eventid FROM events

    Every row shows up, since all the eventid is different, I need something like:

    SELECT DISTINCT(distance, eventname), eventid FROM events

    But the above query doesn't seem to be valid...
    Last edited by Robo; Jul 27, 2001 at 04:33.

  2. #2
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hmm... What's the error?
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  3. #3
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When I type in:

    SELECT DISTINCT(distance, eventname), eventid FROM events;

    I get the error:

    You have an error in your SQL syntax near ' eventname), eventid FROM events' at line 1

  4. #4
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Ergh...

    Over my head, sorry to get your hopes up... Let me look @ some of my code though...

    kk,

    I've done:

    SELECT id,DISTINCT(name) as dname FROM users

    And it's worked on access and sql server.

    That's all I know, sorry.
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  5. #5
    code addict Abstraction's Avatar
    Join Date
    Apr 2001
    Location
    Des Moines, IA
    Posts
    346
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try SELECT DISTINCTdistance, eventname, eventid FROM events;

  6. #6
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Abstraction, I've tried that already in my first post.

    That query gives all of the results, since all the eventid are distinct.

    I need to get the distincts of distance and eventname, plus the eventid for those rows.

  7. #7
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, there is something odd about the logic of what you are wanting to do here. You need some logic to decide which particular row is going to be in the result set when there are two or more rows with the same distance and eventname. You need to provide this logic because as far as a RDMS is concerned, there is no order between records in a table. Things like auto_increment fields give a false impression to the programmer that RDMSs maintain some kind of order between records in a table. This is not so.

    So, lets say that where there are two or more records that have the same distance and eventname, you want the eventid with the MIN value.

    SELECT distance, eventname, MIN(eventid) AS minEventID
    FROM events
    GROUP BY distance, eventname

    That should work ?!? BTW, as a side note, DISTINCT is just shorthand in that MySQL will rewrite a SELECT DISTINCT query into a SELECT ... FROM ... GROUP BY query. You can see whats going on here by taking your SELECT DISTINCT ... FROM ... query and using the EXPLAIN function with it. Eg:

    EXPLAIN SELECT DISCTINCT distance, eventname FROM events

    should throw some light into what is going on under the bonnet.

  8. #8
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, by using this query:

    SELECT DISTINCT distance, eventname FROM events

    I get all the rows I need:

    Code:
    +----------+---------------+
    | distance | eventname     |
    +----------+---------------+
    | 800      | Run           |
    |          | Discuss Throw |
    |          | Javelin Throw |
    | 100      | Dash          |
    | 100      | Relay         |
    | 200      | Dash          |
    |          | Pole Vault    |
    | 400      | Dash          |
    |          | Long Jump     |
    |          | High Jump     |
    +----------+---------------+
    I need to get the eventid for each of those events. In my situation, normalisation for this table is not an available option.

  9. #9
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You seem to be implying that there will be more than one record that might have the same distance, eventname. Eg;
    Code:
    id   distance   eventname
    01   800       Run
    02   200       Dash
    03   800       Run
    Is this the case?

  10. #10
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not quite, on the table, there're different events, some for Junior, some for Senior, etc. such as

    800m Dash Junior Boys
    800m Dash Senior Men
    Pole Vault Senior Women

    See there are three rows, but I only want two of them, the 800m Dash and the Pole Vault.

    The query

    SELECT distance, eventname, MIN(eventid) AS minEventID
    FROM events
    GROUP BY distance, eventname

    seems to work fine for this situation, so thanks a lot for the help!


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
  •