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

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

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 daytime
ORDER BY daytime DESC

Doesn't work. Result-set has 130 rows although database holds 187. Result includes some dublicates 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 daytime

Nope. Gives all the records.

// 3 ..something exotic: 

With various results.

Answer 1

You are so close! All you need to do is select BOTH the home and it's max date time, then join back to the topten table on BOTH fields:

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

Answer 2

This will work even if you have two or more rows for each home with equal DATETIME's:

SELECT id, home, datetime, player, resource
FROM   (
       SELECT (
              SELECT  id
              FROM    topten ti
              WHERE   ti.home = t1.home
              ORDER BY
                      ti.datetime DESC
              LIMIT 1
              ) lid
       FROM   (
              SELECT  DISTINCT home
              FROM    topten
              ) t1
       ) ro, topten t2
WHERE  t2.id = ro.lid

Answer 3

I think this will give you the desired result:

SELECT   home, MAX(datetime)
FROM     my_table
GROUP BY by home

If you need other columns, just make a join with the original table.

Best regards.

Answer 4

Try this

select * from mytable a join
(select home, max(datetime) datetime
from mytable
group by home) b
 on a.home = b.home and a.datetime = b.datetime

Regards K

Answer 5

SELECT  tt.*
FROM    TestTable tt 
INNER JOIN 
        (
        SELECT  coord, MAX(datetime) AS MaxDateTime 
        FROM    rapsa 
        GROUP BY
                krd 
        ) groupedtt
ON      tt.coord = groupedtt.coord
        AND tt.datetime = groupedtt.MaxDateTime

Answer 6

Here goes T-SQL version:

-- Test data
DECLARE @TestTable TABLE (id INT, home INT, date DATETIME, 
  player VARCHAR(20), resource INT)
INSERT INTO @TestTable
SELECT 1, 10, '2009-03-04', 'john', 399 UNION
SELECT 2, 11, '2009-03-04', 'juliet', 244 UNION
SELECT 5, 12, '2009-03-04', 'borat', 555 UNION
SELECT 3, 10, '2009-03-03', 'john', 300 UNION
SELECT 4, 11, '2009-03-03', 'juliet', 200 UNION
SELECT 6, 12, '2009-03-03', 'borat', 500 UNION
SELECT 7, 13, '2008-12-24', 'borat', 600 UNION
SELECT 8, 13, '2009-01-01', 'borat', 700

-- Answer
SELECT id, home, date, player, resource 
FROM (SELECT id, home, date, player, resource, 
    RANK() OVER (PARTITION BY home ORDER BY date DESC) N
    FROM @TestTable
)M WHERE N = 1

-- and if you really want only home with max date
SELECT T.id, T.home, T.date, T.player, T.resource 
    FROM @TestTable T
INNER JOIN 
(   SELECT TI.id, TI.home, TI.date, 
     RANK() OVER (PARTITION BY TI.home ORDER BY TI.date) N
    FROM @TestTable TI
    WHERE TI.date IN (SELECT MAX(TM.date) FROM @TestTable TM)
)TJ ON TJ.N = 1 AND T.id = TJ.id

EDIT
Unfortunately, there are no RANK() OVER function in MySQL.
But it can be emulated, see Emulating Analytic (AKA Ranking) Functions with MySQL.
So this is MySQL version:

SELECT id, home, date, player, resource 
FROM TestTable AS t1 
WHERE 
    (SELECT COUNT(*) 
      FROM TestTable AS t2 
      WHERE t2.home = t1.home AND t2.date > t1.date
    ) = 0

Answer 7

This works on Oracle:

with table_max as(
  select id
       , home
       , datetime
       , player
       , resource
       , max(home) over (partition by home) maxhome
    from table  
)
select id
     , home
     , datetime
     , player
     , resource
  from table_max
 where home = maxhome

Answer 8

You can also try this and for large tables your performance will be better.

SELECT t1.id, t1.home, t1.date, t1.player, t1.resource
FROM   t_scores_1 t1 
INNER JOIN t_scores_1 t2
   ON t1.home = t2.home
WHERE t1.date > t2.date

Answer 9

THANK YOU VERY MUCHHHHH Michael La Voie :-D

Answer 10

thank you for your valuable information, this is exactly what i need,

can you write the query for maximum time and maximum date ?

Thanks Rameshkumar MCA