By Kaptah


2009-03-04 20:14:26 8 Comments

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.

17 comments

@axiac 2015-01-06 16:23:49

The fastest MySQL solution, without inner queries and without GROUP BY:

SELECT m.*                    -- get the row that contains the max value
FROM topten m                 -- "m" from "max"
    LEFT JOIN topten b        -- "b" from "bigger"
        ON m.home = b.home    -- match "max" row with "bigger" row by `home`
        AND m.datetime < b.datetime           -- want "bigger" than "max"
WHERE b.datetime IS NULL      -- keep only if there is no bigger than max

Explanation:

Join the table with itself using the home column. The use of LEFT JOIN ensures all the rows from table m appear in the result set. Those that don't have a match in table b will have NULLs for the columns of b.

The other condition on the JOIN asks to match only the rows from b that have bigger value on the datetime column than the row from m.

Using the data posted in the question, the LEFT JOIN will produce this pairs:

+------------------------------------------+--------------------------------+
|              the row from `m`            |    the matching row from `b`   |
|------------------------------------------|--------------------------------|
| id  home  datetime     player   resource | id    home   datetime      ... |
|----|-----|------------|--------|---------|------|------|------------|-----|
| 1  | 10  | 04/03/2009 | john   | 399     | NULL | NULL | NULL       | ... | *
| 2  | 11  | 04/03/2009 | juliet | 244     | NULL | NULL | NULL       | ... | *
| 5  | 12  | 04/03/2009 | borat  | 555     | NULL | NULL | NULL       | ... | *
| 3  | 10  | 03/03/2009 | john   | 300     | 1    | 10   | 04/03/2009 | ... |
| 4  | 11  | 03/03/2009 | juliet | 200     | 2    | 11   | 04/03/2009 | ... |
| 6  | 12  | 03/03/2009 | borat  | 500     | 5    | 12   | 04/03/2009 | ... |
| 7  | 13  | 24/12/2008 | borat  | 600     | 8    | 13   | 01/01/2009 | ... |
| 8  | 13  | 01/01/2009 | borat  | 700     | NULL | NULL | NULL       | ... | *
+------------------------------------------+--------------------------------+

Finally, the WHERE clause keeps only the pairs that have NULLs in the columns of b (they are marked with * in the table above); this means, due to the second condition from the JOIN clause, the row selected from m has the biggest value in column datetime.

Read the SQL Antipatterns: Avoiding the Pitfalls of Database Programming book for other SQL tips.

@Ronnie 2015-03-20 11:42:37

Nice use of borat

@Thomas Tempelmann 2015-04-30 10:29:16

With SQLite, the first one is much much slower than La Voie's version when there is no index on the matched column (i.e. "home"). (Tested with 24k rows resulting in 13k rows)

@TlmaK0 2016-08-10 11:40:37

This is the best answer, if you show the execution plan you will see one step less with this query

@Istiaque Ahmed 2017-11-08 12:57:30

what will happen if 2 rows have the same home and datetime and the datetime is the maximum for that particular home ?

@axiac 2017-11-08 13:10:45

Both rows appears in the result set. This answer is a proof of concept. In your real code you probably have another criterion to select only one of them in this situation (maybe the first one or the last one or you use another column to decide). Just add this criterion as a new condition in the ON clause. F.e. ... ON ... AND m.id < b.id to keep the most recent entry (the one with the greatest id) when two rows have the same values in home and datetime columns and it is the maximum datetime.

@Michael La Voie 2009-03-04 20:22:47

You are so close! All you need to do is select BOTH the home and its 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

@Maksym Gontar 2009-03-04 21:04:33

Test it for distinct, if two equal max datetime be in the same home (with different players)

@Kaptah 2009-03-06 17:22:57

player:home = 1:N

@Parker 2010-10-22 17:42:35

I think the classic way to do this is with a natural join: "SELECT tt.* FROM topten tt NATURAL JOIN ( SELECT home, MAX(datetime) AS datetime FROM topten GROUP BY home ) mostrecent;" Same query exactly, but arguably more readable

@Randell 2011-08-01 04:03:19

@max-gontar, how would you test for distinct if it's not player:home = 1:N?

@Maksym Gontar 2011-08-01 07:25:44

@Randall, not sure if I got your comment question right, please ask new SO question (more opportunities for description and more chances it will be answered), thanks!

@Daan 2014-07-10 11:01:03

If it is a very large table it would take ages if there is no index on home.

@dani-h 2014-07-10 12:47:49

How would this query look in SQLite?

@shimizu 2014-08-06 14:14:15

I'm confused where the table tt comes from?

@Young 2014-11-13 03:10:16

why not SELECT home, MAX(datetime) AS MaxDateTime,player,resource FROM topten GROUP BY home,am I miss something?

@Kemal Duran 2015-06-01 12:10:28

what about if there are two rows which have same 'home' and 'datetime' field values?

@me1111 2015-07-09 15:45:44

Can somebody tell please why not select * from topten GROUP BY home ORDER BY datetime DESC ?

@sactiw 2015-11-26 12:07:40

@Young the problem with your query is that it may return id, player and resource of non-max row for a given home i.e. for home = 10 you may get : 3 | 10 | 04/03/2009 | john | 300 In other words it doesn't guarantees that all column of a row in resultset will belong to max(datetime) for given home.

@sactiw 2015-11-26 12:11:47

@me1111 problem with your query is that it may/may not return row ith max(datetime) for a given home. Reason being GROUP BY will fetch any random row for each home and ORDER BY will just sort the overall all result as produced by GROUP BY

@MBahamondes 2015-12-28 19:07:41

Very nice, thank you!!

@SeldomNeedy 2016-04-08 00:34:13

@Parker Using a NATURAL JOIN like that may work for this (simple) case, but is not generally reliable, as it won't always eliminate all the rows it should for more complicated tables. For production views, it is safer to explicitly specify the join-condition.

@SeldomNeedy 2016-04-08 01:03:19

@KemalDuran so-called 'window functions' are probably the best way to handle this situation in modern databases (if it's possible that two or more rows may have identical values in both the "home" and "datetime" fields). AFAIK MySQL lacks these but others such as Postgres now have them.

@greaterKing 2016-06-08 20:00:01

Many people have pointed out issues offering no counter example that shows a better solution...just makes the reader hesitant on implementing the answer.

@cardamom 2017-06-21 08:17:33

Regarding @KemalDuran 's comment above , if there are two rows with the same home and datetime fields, what you need to do is take Michael La Voie 's solution and add MAX(id) AS MaxID to the inner SELECT statement and then go and add another line AND tt.id = groupedtt.MaxID at the end.

@Istiaque Ahmed 2017-11-08 12:43:29

@cardamom, why should that pick only the distinct values ?

@Istiaque Ahmed 2017-11-08 12:54:02

@sactiw, I could not understand what you said as a reply to Young's comment. Let me quote from your comment :'the problem with your query is that it may return id, player and resource of non-max row for a given home'- why should that happen as GROUP BY is applied and MAX(datetime) is picked up ?

@sactiw 2017-11-08 13:12:44

@IstiaqueAhmed his query would return "Maximum of Certain Column per Group" but what we really need here is "The Rows Holding the Group-wise Maximum of a Certain Column". For further details refer examples under: mysql queries

@cardamom 2017-11-08 13:14:58

@IstiaqueAhmed it took me a short while to remember what I was doing when I used this solution and wrote that comment. @KemalDuran asked what about if there are two rows which have same 'home' and 'datetime' field values? My comment above adds a 3rd grouping to fix that problem, around the id field which is definitely a unique one unlike say datetime or player. @MichaelLaVoie 's solution has 2 groupings and some of us needed this solution for situations where that was not enough.

@M Khalid Junaid 2017-11-05 13:35:40

Another way to gt the most recent row per group using a sub query which basically calculates a rank for each row per group and then filter out your most recent rows as with rank = 1

select a.*
from topten a
where (
  select count(*)
  from topten b
  where a.home = b.home
  and a.`datetime` < b.`datetime`
) +1 = 1

DEMO

Here is the visual demo for rank no for each row for better understanding

By reading some comments what about if there are two rows which have same 'home' and 'datetime' field values?

Above query will fail and will return more than 1 rows for above situation. To cover up this situation there will be a need of another criteria/parameter/column to decide which row should be taken which falls in above situation. By viewing sample data set i assume there is a primary key column id which should be set to auto increment. So we can use this column to pick the most recent row by tweaking same query with the help of CASE statement like

select a.*
from topten a
where (
  select count(*)
  from topten b
  where a.home = b.home
  and  case 
       when a.`datetime` = b.`datetime`
       then a.id < b.id
       else a.`datetime` < b.`datetime`
       end
) + 1 = 1

DEMO

Above query will pick the row with highest id among the same datetime values

visual demo for rank no for each row

@Manoj Kargeti 2017-05-08 06:27:47

@Michae The accepted answer will working fine in most of the cases but it fail for one for as below.

In case if there were 2 rows having HomeID and Datetime same the query will return both rows, not distinct HomeID as required, for that add Distinct in query as below.

SELECT DISTINCT tt.home  , tt.MaxDateTime
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

@Istiaque Ahmed 2017-11-08 12:33:43

result shows - "#1054 - Unknown column 'tt.MaxDateTime' in 'field list'"

@Manoj Kargeti 2017-11-08 17:11:19

@IstiaqueAhmed do you have MaxDatetime filed i.e any column name like that..?

@Istiaque Ahmed 2017-11-08 17:49:10

No, the table in OP does not have any such column.

@Manoj Kargeti 2017-11-09 13:52:19

the error also saying the same please..what exactly you wanna do ? can u send the table structure and your query.

@Vijunav Vastivch 2016-07-14 06:45:29

this is the query you need:

 SELECT b.id, a.home,b.[datetime],b.player,a.resource FROM
 (SELECT home,MAX(resource) AS resource FROM tbl_1 GROUP BY home) AS a

 LEFT JOIN

 (SELECT id,home,[datetime],player,resource FROM tbl_1) AS b
 ON  a.resource = b.resource WHERE a.home =b.home;

@Istiaque Ahmed 2017-11-08 12:35:28

can you explain your answer ?

@Ricardo Felgueiras 2009-03-04 20:30:58

I think this will give you the desired result:

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

BUT if you need other columns as well, just make a join with the original table (check Michael La Voie answer)

Best regards.

@Quassnoi 2009-03-04 20:34:03

He needs other columns also.

@Ricardo Felgueiras 2009-03-04 20:38:41

What columns he needs??

@Quassnoi 2009-03-06 10:32:26

id, home, datetime, player, resource

@Roland 2015-10-03 10:20:10

Why not using: SELECT home, MAX(datetime) AS MaxDateTime,player,resource FROM topten GROUP BY home Did I miss something?

@simpleuser 2015-11-07 04:26:46

That would only be valid with MySQL, and only versions before 5.7 (?) or after 5.7 with ONLY_FULL_GROUP_BY disabled, since it is SELECTing columns that have not been aggregated/GROUPed (player, resource) which means MySQL will provide randomly chosen values for those two result fields. It would not be a problem for the player column since that correlates to the home column, but the resource column would not correlate with the home or datetime column and you could not guarantee which resource value you'd receive.

@sactiw 2015-11-26 13:08:29

+1 for the explanation, BUT w.r.t the asked question this query won't return the expected output in MySQL version 5.6 and before and I highly doubt it to behave otherwise in MySQL version 5.7 and after.

@Istiaque Ahmed 2017-11-08 13:07:56

@simpleuser, ` It would not be a problem for the player column since that correlates to the home column` - can you explain more ?

@simpleuser 2017-11-08 21:46:11

@IstiaqueAhmed as I look at it again, that statement is incorrect. I had thought each player always had the same home value, but I see now that they do not, so the same random select issue will occur for that column as well

@Shiva 2010-02-16 20:51:50

You can also try this one and for large tables query performance will be better. It works when there no more than two records for each home and their dates are different. Better general MySQL query is one from Michael La Voie above.

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

Or in case of Postgres or those dbs that provide analytic functions try

SELECT t.* FROM 
(SELECT t1.id, t1.home, t1.date, t1.player, t1.resource
  , row_number() over (partition by t1.home order by t1.date desc) rw
 FROM   topten t1 
 INNER JOIN topten t2
   ON t1.home = t2.home
 WHERE t1.date > t2.date 
) t
WHERE t.rw = 1

@marcin93w 2014-07-14 14:48:03

Is this answer correct? I tried to use it, but it seams not to select the record with newest date for 'home', but only removes record with oldest date. Here's an example: SQLfiddle

@Shiva 2014-07-16 21:02:05

@kidOfDeath - Updated my reply with context and Postgres query

@Thomas Tempelmann 2015-04-30 10:28:36

With SQLite, the first one is much much slower than La Voie's version when there is no index on the matched column (i.e. "home").

@Jason Heo 2014-02-01 09:12:32

Here is MySQL version which prints only one entry where there are duplicates MAX(datetime) in a group.

You could test here http://www.sqlfiddle.com/#!2/0a4ae/1

Sample Data

mysql> SELECT * from topten;
+------+------+---------------------+--------+----------+
| id   | home | datetime            | player | resource |
+------+------+---------------------+--------+----------+
|    1 |   10 | 2009-04-03 00:00:00 | john   |      399 |
|    2 |   11 | 2009-04-03 00:00:00 | juliet |      244 |
|    3 |   10 | 2009-03-03 00:00:00 | john   |      300 |
|    4 |   11 | 2009-03-03 00:00:00 | juliet |      200 |
|    5 |   12 | 2009-04-03 00:00:00 | borat  |      555 |
|    6 |   12 | 2009-03-03 00:00:00 | borat  |      500 |
|    7 |   13 | 2008-12-24 00:00:00 | borat  |      600 |
|    8 |   13 | 2009-01-01 00:00:00 | borat  |      700 |
|    9 |   10 | 2009-04-03 00:00:00 | borat  |      700 |
|   10 |   11 | 2009-04-03 00:00:00 | borat  |      700 |
|   12 |   12 | 2009-04-03 00:00:00 | borat  |      700 |
+------+------+---------------------+--------+----------+

MySQL Version with User variable

SELECT *
FROM (
    SELECT ord.*,
        IF (@prev_home = ord.home, 0, 1) AS is_first_appear,
        @prev_home := ord.home
    FROM (
        SELECT t1.id, t1.home, t1.player, t1.resource
        FROM topten t1
        INNER JOIN (
            SELECT home, MAX(datetime) AS mx_dt
            FROM topten
            GROUP BY home
          ) x ON t1.home = x.home AND t1.datetime = x.mx_dt
        ORDER BY home
    ) ord, (SELECT @prev_home := 0, @seq := 0) init
) y
WHERE is_first_appear = 1;
+------+------+--------+----------+-----------------+------------------------+
| id   | home | player | resource | is_first_appear | @prev_home := ord.home |
+------+------+--------+----------+-----------------+------------------------+
|    9 |   10 | borat  |      700 |               1 |                     10 |
|   10 |   11 | borat  |      700 |               1 |                     11 |
|   12 |   12 | borat  |      700 |               1 |                     12 |
|    8 |   13 | borat  |      700 |               1 |                     13 |
+------+------+--------+----------+-----------------+------------------------+
4 rows in set (0.00 sec)

Accepted Answers' outout

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
+------+------+---------------------+--------+----------+
| id   | home | datetime            | player | resource |
+------+------+---------------------+--------+----------+
|    1 |   10 | 2009-04-03 00:00:00 | john   |      399 |
|    2 |   11 | 2009-04-03 00:00:00 | juliet |      244 |
|    5 |   12 | 2009-04-03 00:00:00 | borat  |      555 |
|    8 |   13 | 2009-01-01 00:00:00 | borat  |      700 |
|    9 |   10 | 2009-04-03 00:00:00 | borat  |      700 |
|   10 |   11 | 2009-04-03 00:00:00 | borat  |      700 |
|   12 |   12 | 2009-04-03 00:00:00 | borat  |      700 |
+------+------+---------------------+--------+----------+
7 rows in set (0.00 sec)

@Radek 2016-12-01 09:34:14

Altho I love this answer, as this is helping me so much, I have to point to one major flaw, that it dependat on used mysql system. Basically, this solution relies on ORDER BY clause in subselect. This MIGHT, or MIGHT NOT work in various mysql environment. I haven't tried it on pure MySQL, but for sure this doesn't work RELIABLY on MariaDB 10.1, as explained here stackoverflow.com/questions/26372511/… but the very same code does work ok on Percona Server. To be precise, you MIGHT, or MIGHT NOT get the same results, depending on amount of t1 columns.

@Radek 2016-12-01 09:38:50

The example for this statement is, that on MariaDB 10.1 it worked, when I used 5 columns from t1 table. As soon as I added sixth column, obviously messing with the "natural" data sort in original table, it stopped working. The reason is, the data in subselect became un-ordered and thus I had "is_first_appear=1" condition met several times. The very same code, with same data, worked on Percona ok.

@SysDragon 2014-01-17 12:11:29

Try this for SQL Server:

WITH cte AS (
   SELECT home, MAX(year) AS year FROM Table1 GROUP BY home
)
SELECT * FROM Table1 a INNER JOIN cte ON a.home = cte.home AND a.year = cte.year

@Jr. 2012-07-12 16:11:16

SELECT c1, c2, c3, c4, c5 FROM table1 WHERE c3 = (select max(c3) from table)

SELECT * FROM table1 WHERE c3 = (select max(c3) from table1)

@najeeb 2014-07-16 09:27:13

this one worked

@MJB 2010-12-05 17:04:14

Since people seem to keep running into this thread (comment date ranges from 1.5 year) isn't this much simpler:

SELECT * FROM (SELECT * FROM topten ORDER BY datetime DESC) tmp GROUP BY home

No aggregation functions needed...

Cheers.

@Fowl 2011-10-25 00:58:49

This doesn't seem to work. Error Message: Column 'x' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

@nowhere 2013-04-25 14:17:57

lol total win :)

@ErikE 2014-05-22 00:44:55

This definitely won't work in SQL Server or Oracle, though it looks like it might work in MySQL.

@wayofthefuture 2016-05-15 22:19:34

This is really beautiful! How does this work? By using DESC and the default group return column? So if I changed it to datetime ASC, it would return the earliest row for each home?

@Dog Lover 2016-08-11 12:22:29

This is brilliant!

@user3562927 2017-04-17 06:53:54

This straight-up doesn't work if you have nonaggregated columns (in MySQL).

@Kaptah 2009-03-04 20:58:54

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

@Quassnoi 2009-03-04 20:24:20

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

@Quassnoi 2009-03-06 08:36:02

We really need this DIV to check for compilation errors :)

@Kaptah 2009-03-06 17:19:05

added lid field in table, No Good

@Kaptah 2009-03-24 15:47:35

This one didn't execute on PHPMyAdmin. Page refreshes but there's no result nor error..?

@Alexandre 2017-05-09 06:41:54

You save me, thx a lot !

@Istiaque Ahmed 2017-11-08 13:00:38

WHERE ti.home = t1.home - can you explain the syntax ?

@Quassnoi 2017-11-08 13:23:05

@IstiaqueAhmed: what exactly is that you don't understand here? It's a correlated query, and the expression you mention is a correlation condition.

@Istiaque Ahmed 2017-11-08 16:05:03

@Quassnoi, The select query that has the line WHERE ti.home = t1.home does not need the FROM clause that defines t1. So how is it used ?

@Quassnoi 2017-11-08 18:18:00

@Maksym Gontar 2009-03-04 20:59:52

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

@Kaptah 2009-03-05 23:01:24

sorry dude, #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( ) OVER ( PARTITION BY krd ORDER BY daytime DESC ) N FROM @rapsa ) M WHERE N = ' at line 1

@Maksym Gontar 2009-03-06 07:53:18

ah, so you're using MySQL. That's what you should start from! I will update answer soon.

@egidiocs 2011-11-11 03:44:03

@MaxGontar, your mysql solution rocks, thx. what if in your @_TestTable you remove row#1>: SELECT 1, 10, '2009-03-04', 'john', 399 , this is, what if you have a single row for a given home value? thx.

@MikeTeeVee 2013-01-10 00:44:24

BUG: Replace "RANK()" with "ROW_NUMBER()". If you have a tie (caused by a duplicate date value) you will have two records with "1" for N.

@FerranB 2009-03-05 23:19:39

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

@n00b 2013-04-23 21:59:03

how does this pick the max datetime? he asked to group by home, and select max datetime. I dont see how this does that.

@Khb 2009-03-04 20:41:52

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

@Maksym Gontar 2009-03-04 21:03:21

Test it for distinct, if two equal max datetime be in the same home (with different players)

@Istiaque Ahmed 2017-11-08 12:39:04

the alias for max(datetime) is datetime. Won't it make any problem ?

@Istiaque Ahmed 2017-11-08 13:04:25

How is the highest datetime selected ?

Related Questions

Sponsored Content

27 Answered Questions

[SOLVED] SQL select only rows with max value on a column

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

24 Answered Questions

[SOLVED] How do I perform an IF...THEN in an SQL SELECT?

5 Answered Questions

[SOLVED] How do I (or can I) SELECT DISTINCT on multiple columns?

44 Answered Questions

9 Answered Questions

[SOLVED] SQL to find the number of distinct values in a column

  • 2008-09-26 19:52:48
  • Christian Oudard
  • 469720 View
  • 300 Score
  • 9 Answer
  • Tags:   sql distinct

32 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

34 Answered Questions

[SOLVED] Fetch the row which has the Max value for a column

37 Answered Questions

8 Answered Questions

[SOLVED] 'IF' in 'SELECT' statement - choose output value based on column values

  • 2011-05-10 13:53:31
  • Michael
  • 1264498 View
  • 622 Score
  • 8 Answer
  • Tags:   mysql sql database

Sponsored Content