How to select a DISTINCT

Discussion in 'Databases' started by aaron_nimocks, May 24, 2009.

0
  1. #1
    Im making a quiz script for work and having an issue.

    Basically I am trying to return 30 top high scores of the quiz but only want to show the top score for each user. I know I have to use distinct but everything I try is not working.

    mysql_query("SELECT * FROM scores WHERE examid = '$cat' ORDER BY score DESC LIMIT 0, 30");

    Now I want to add DISTINCT userid into that but really have no clue. Right now that returns all scores (high to low) for every userid.

    Thanks!
     
    aaron_nimocks, May 24, 2009 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    A GROUP BY clause will filter to only one result per user.

    Something like this:

    mysql_query("SELECT * FROM scores WHERE examid = '$cat' GROUP BY userid ORDER BY score DESC LIMIT 0, 30");

    This will show one result per user. The result shown will be the one with the highest score per user.
     
    jestep, May 24, 2009 IP
    aaron_nimocks likes this.
  3. dannyrich

    dannyrich Peon

    Messages:
    27
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    I also think using GROUP BY clause will solve ur problem in order to get distinct userid which means one result per user will be shown.....so u can use it I am sure this will help you to get the desired result........
     
    dannyrich, May 24, 2009 IP
  4. aaron_nimocks

    aaron_nimocks Im kind of a big deal Staff

    Messages:
    5,563
    Likes Received:
    627
    Best Answers:
    0
    Trophy Points:
    420
    #4
    Jestep

    Thanks that was almost what I needed and just changed it around some to work.

    mysql_query("SELECT *, MAX(score) FROM scores WHERE examid = '$cat' GROUP BY userid ORDER BY MAX(score) DESC LIMIT 0, 30");

    is what ended up working.
     
    aaron_nimocks, May 25, 2009 IP