MySQL Problem

Discussion in 'Databases' started by Sulan84, Oct 2, 2010.

0
  1. #1
    Hi.
    I need help with my QueryString....

    "SELECT fbid, points, time, pic, name FROM `myTable` WHERE country='de' ORDER BY points DESC, time ASC LIMIT 10"
    Code (markup):
    My Problem is that fbid results should be unique with best points (points DESC) and best time (time ASC).
    With "SELECT DISTINCT fbid, points.... " i get double "fbid's" too.?

    How can solve this?
     
    Sulan84, Oct 2, 2010 IP
  2. Sulan84

    Sulan84 Member

    Messages:
    121
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    28
    #2
    no ideas?

    ...
     
    Sulan84, Oct 3, 2010 IP
  3. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #3
    Try:

    "SELECT fbid, points, time, pic, name FROM `myTable` WHERE country='de' GROUP BY fbid ORDER BY points DESC, time ASC LIMIT 10"
     
    jestep, Oct 4, 2010 IP
  4. plog

    plog Peon

    Messages:
    298
    Likes Received:
    11
    Best Answers:
    1
    Trophy Points:
    0
    #4
    jestep's query is exactly what you asked for, but not what I think you want. Try and put into plain words what you are trying to get out--don't use field names, don't use SQL terms, just plain words. Here's a plain language statement of what I think you want:

    >> The lowest time and highest score for each individual.

    If that's correct, here's the query to do it:

    SELECT fbid, MAX(points) AS bestpoints, MIN(time) AS besttime, name FROM myTable WHERE country='de' ORDER BY MAX(points) DESC, MIN(time) ASC LIMIT10;
    PHP:
    If not, correct my plain language statement to make it say what you want.
     
    plog, Oct 4, 2010 IP