Select earliest member based on min(date)

pearl2 · #1 (**permalink**) 04-19-08, 02:32

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

r937 · #2 (**permalink**) 04-19-08, 06:59

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

pearl2 · #3 (**permalink**) 04-19-08, 08:49

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"...

r937 · #4 (**permalink**) 04-19-08, 13:12

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

pearl2 · #5 (**permalink**) 04-20-08, 01:56

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

r937 · #6 (**permalink**) 04-20-08, 04:09

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

pearl2 · #7 (**permalink**) 04-20-08, 04:55

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     |
+-----------+---------+---------------------+----------+

r937 · #8 (**permalink**) 04-20-08, 07:08

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

pearl2 · #9 (**permalink**) 04-20-08, 08:41

Thank you so much, r937!

Works like champ now

Internet Services

Web Development

Digital Marketing

Consumer Tech