Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: Select earliest member based on min(date)

    Hi,

    I would like to be able to select the earliest solver of a game. For example, given:

    Code:
    +-----------+---------+---------------------+
    | member_id | game_id | solved              |
    +-----------+---------+---------------------+
    |         1 |       1 | 2007-11-14 01:05:15 |
    |         2 |       1 | 2007-11-15 16:45:34 |
    |         3 |       1 | 2007-11-16 12:47:47 |
    |         2 |       2 | 2008-01-02 22:53:46 |
    |         3 |       2 | 2008-01-16 17:08:43 |
    |         1 |       2 | 2008-01-29 02:19:18 |
    +-----------+---------+---------------------+
    
    +-----------+----------+
    | member_id | username |
    +-----------+----------+
    |         1 | john     |
    |         2 | pete     |
    |         3 | kate     |
    +-----------+----------+
    
    CREATE TABLE `test_members` (
      `member_id` mediumint,
      `username` varchar(20)
    );
    CREATE TABLE `test_hofs` (
      `member_id` mediumint,
      `game_id` tinyint,
      `solved` datetime
    );
    INSERT INTO test_hofs VALUES (1, 1, '2007-11-14 01:05:15.00'), (2, 1, '2007-11-15 16:45:34.00'), (3, 1, '2007-11-16 12:47:47.00'), (2, 2, '2008-01-02 22:53:46.00'), (3, 2, '2008-01-16 17:08:43.00'), (1, 2, '2008-01-29 02:19:18.00');
    INSERT INTO test_members VALUES (1, 'john'), (2, 'pete'), (3, 'kate');
    The sql should return "john" for game 1 and "pete" for game 2.

    Could someone please enlighten me?

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    create a query that will find the earliest date for each game

    hint: it will involve MIN() and GROUP BY

    then wrap parentheses around this query, put AS mindates after it, and join this back to the table on game_id and mindate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thank you, r937

    I tried this but it doesn't work:

    Code:
    SELECT username, (SELECT MIN(solved) FROM test_hofs GROUP BY game_id) AS mindates
    FROM  test_members
    JOIN test_hofs ON test_hofs.member_id=test_members.member_id
    I don't quite understand the part "and join this back to the table on game_id and mindate"...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, let's break it down

    the query you want to start with is this one:
    Code:
    SELECT game_id
         , MIN(solved) AS minsolved
      FROM test_hofs 
    GROUP 
      BY game_id
    run that and tell me what you see

    okay, now let's wrap another query around it:
    Code:
    SELECT mindates.* 
      FROM ( SELECT game_id
                  , MIN(solved) AS minsolved
               FROM test_hofs 
             GROUP 
               BY game_id ) AS mindates
    run that and tell me what you see
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thanks again, r937.

    The output I got from your 2 sql's are the same:

    Code:
    +---------+---------------------+
    | game_id | minsolved           |
    +---------+---------------------+
    |       1 | 2007-11-14 01:05:15 |
    |       2 | 2008-01-02 22:53:46 |
    +---------+---------------------+
    Now I need a JOIN to test_members to get the corresponding usernames...that's the hard part for me

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's easy, just take the last query from post #4 and join it back to your table
    Code:
    SELECT test_hofs.* 
      FROM ( SELECT game_id
                  , MIN(solved) AS minsolved
               FROM test_hofs 
             GROUP 
               BY game_id ) AS mindates
    INNER
      JOIN test_hofs
        ON test_hofs.game_id = mindates.game_id
       AND test_hofs.solved  = mindates.minsolved
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    I added a JOIN to test_members to get the corresponding usernames. I got 3 sets of the same result:

    Code:
    SELECT test_hofs.* 
      FROM ( SELECT game_id
                  , MIN(solved) AS minsolved
               FROM test_hofs 
             GROUP 
               BY game_id ) AS mindates
    INNER
      JOIN test_hofs
        ON test_hofs.game_id = mindates.game_id
       AND test_hofs.solved  = mindates.minsolved
    INNER
      JOIN test_members ON test_hofs.member_id = test_hofs.member_id
    
    +-----------+---------+---------------------+----------+
    | member_id | game_id | solved              | username |
    +-----------+---------+---------------------+----------+
    |         1 |       1 | 2007-11-14 01:05:15 | john     |
    |         1 |       1 | 2007-11-14 01:05:15 | pete     |
    |         1 |       1 | 2007-11-14 01:05:15 | kate     |
    |         2 |       2 | 2008-01-02 22:53:46 | john     |
    |         2 |       2 | 2008-01-02 22:53:46 | pete     |
    |         2 |       2 | 2008-01-02 22:53:46 | kate     |
    +-----------+---------+---------------------+----------+
    How do I reduce it to one set?

    Code:
    +-----------+---------+---------------------+----------+
    | member_id | game_id | solved              | username |
    +-----------+---------+---------------------+----------+
    |         1 |       1 | 2007-11-14 01:05:15 | john     |
    |         1 |       1 | 2007-11-14 01:05:15 | pete     |
    +-----------+---------+---------------------+----------+

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pearl2
    How do I reduce it to one set?
    by fixing the incorrect join condition

    you joined the data rows to every row in the members table, because your join condition was
    Code:
    ON test_hofs.member_id = test_hofs.member_id
    which is true for every row

    you probably want this instead:
    Code:
    ON test_hofs.member_id = test_members.member_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thank you so much, r937!

    Works like champ now

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •