SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with test top list

    Hello,

    I have table with test results. Every user have 3 records in the table RESULTS, but I need query to select only one(best) result for every user.

    Table RESULTS contains fields USERID (int), TEST_POINTS (int), TEST_TIME (int);

    I need query to sort up results, but only the best result per user.
    Best result for user is one with MAX points for less (MIN) time. So if there are two or more users with the same POINTS, the better one is one with smaller time.
    Sorry for my bad english and tnx!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT m.userid
         , m.max_pts
         , MIN(results.test_time) AS min_time
      FROM ( SELECT userid
                  , MAX(test_points) AS max_pts
               FROM results
             GROUP
                 BY userid ) AS m
    INNER
      JOIN results
        ON results.userid = m.userid
       AND results.test_points = m.max_pts
    GROUP
        BY m.userid
         , m.max_pts
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, tnx! That is logic I needed badly


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
  •