How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

Question

My table is:

id  home  datetime     player   resource

---|-----|------------|--------|---------

1  | 10  | 04/03/2009 | john   | 399 

2  | 11  | 04/03/2009 | juliet | 244

5  | 12  | 04/03/2009 | borat  | 555

3  | 10  | 03/03/2009 | john   | 300

4  | 11  | 03/03/2009 | juliet | 200

6  | 12  | 03/03/2009 | borat  | 500

7  | 13  | 24/12/2008 | borat  | 600

8  | 13  | 01/01/2009 | borat  | 700

I need to select each distinct home holding the maximum value of datetime.

Result would be:

id  home  datetime     player   resource 

---|-----|------------|--------|---------

1  | 10  | 04/03/2009 | john   | 399

2  | 11  | 04/03/2009 | juliet | 244

5  | 12  | 04/03/2009 | borat  | 555

8  | 13  | 01/01/2009 | borat  | 700

I have tried:

-- 1 ..by the MySQL manual: 

SELECT DISTINCT

  home,

  id,

  datetime AS dt,

  player,

  resource

FROM topten t1

WHERE datetime = (SELECT

  MAX(t2.datetime)

FROM topten t2

GROUP BY home)

GROUP BY datetime

ORDER BY datetime DESC

Doesn't work. Result-set has 130 rows although database holds 187. Result includes some duplicates of home.

-- 2 ..join

SELECT

  s1.id,

  s1.home,

  s1.datetime,

  s1.player,

  s1.resource

FROM topten s1

JOIN (SELECT

  id,

  MAX(datetime) AS dt

FROM topten

GROUP BY id) AS s2

  ON s1.id = s2.id

ORDER BY datetime 

Nope. Gives all the records.

-- 3 ..something exotic: 

With various results.

Answer 1

Select BOTH the home and its max date time, then join back to the top table on BOTH the fields:

Want to learn SQL from basics! Here's the right video for you on SQL provided by Intellipaat:

SELECT tt.*

FROM topten tt

INNER JOIN (SELECT home, MAX(datetime) AS MaxDateTime

    FROM topten

    GROUP BY home) groupedtt

ON tt.home = groupedtt.home

AND tt.datetime = groupedtt.MaxDateTime

To learn in-depth about SQL Commands, sign up for an online SQL certification.