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.

Answer 2

Assign a number to each row based on the date within each home group, starting with the most recent date first. After that, just choose the rows with the number 1.These will be the most up-to-date entries for each home.

To implement this refer the below code:

SELECT id, home, datetime, player, resource

FROM (

    SELECT *,

           ROW_NUMBER() OVER (PARTITION BY home ORDER BY datetime DESC) AS row_num

    FROM your_table

) AS ranked

WHERE row_num = 1;

It will give you the output:

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