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.
Related Questions
Sponsored Content
27 Answered Questions
[SOLVED] SQL select only rows with max value on a column
- 2011-10-12 19:42:07
- Majid Fouladpour
- 897779 View
- 998 Score
- 27 Answer
- Tags: mysql sql aggregate-functions greatest-n-per-group groupwise-maximum
28 Answered Questions
[SOLVED] How can I prevent SQL injection in PHP?
- 2008-09-12 23:55:00
- Andrew G. Johnson
- 1528850 View
- 2779 Score
- 28 Answer
- Tags: php mysql sql security sql-injection
24 Answered Questions
[SOLVED] How do I perform an IF...THEN in an SQL SELECT?
- 2008-09-15 14:34:09
- Eric Labashosky
- 3266655 View
- 1336 Score
- 24 Answer
- Tags: sql sql-server tsql if-statement case
5 Answered Questions
[SOLVED] How do I (or can I) SELECT DISTINCT on multiple columns?
- 2008-09-10 15:33:10
- sheats
- 796326 View
- 352 Score
- 5 Answer
- Tags: sql postgresql sql-update duplicates distinct
44 Answered Questions
[SOLVED] How to concatenate text from multiple rows into a single text string in SQL server?
- 2008-10-11 23:49:59
- JohnnyM
- 1958241 View
- 1637 Score
- 44 Answer
- Tags: sql sql-server csv string-concatenation group-concat
9 Answered Questions
32 Answered Questions
[SOLVED] How do I UPDATE from a SELECT in SQL Server?
- 2010-02-25 14:36:53
- jamesmhaley
- 3684906 View
- 3241 Score
- 32 Answer
- Tags: sql sql-server tsql select
34 Answered Questions
[SOLVED] Fetch the row which has the Max value for a column
- 2008-09-23 14:34:13
- Umang
- 533718 View
- 522 Score
- 34 Answer
- Tags: sql oracle greatest-n-per-group
37 Answered Questions
[SOLVED] Add a column with a default value to an existing table in SQL Server
- 2008-09-18 12:30:04
- Mathias
- 2520006 View
- 2443 Score
- 37 Answer
- Tags: sql sql-server sql-server-2005 sql-server-2000
17 comments
@axiac 2015-01-06 16:23:49
The fastest
MySQL
solution, without inner queries and withoutGROUP BY
:Explanation:
Join the table with itself using the
home
column. The use ofLEFT JOIN
ensures all the rows from tablem
appear in the result set. Those that don't have a match in tableb
will haveNULL
s for the columns ofb
.The other condition on the
JOIN
asks to match only the rows fromb
that have bigger value on thedatetime
column than the row fromm
.Using the data posted in the question, the
LEFT JOIN
will produce this pairs:Finally, the
WHERE
clause keeps only the pairs that haveNULL
s in the columns ofb
(they are marked with*
in the table above); this means, due to the second condition from theJOIN
clause, the row selected fromm
has the biggest value in columndatetime
.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
anddatetime
and thedatetime
is the maximum for that particularhome
?@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 greatestid
) when two rows have the same values inhome
anddatetime
columns and it is the maximumdatetime
.@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:@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
andresource
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 innerSELECT
statement and then go and add another lineAND 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 andMAX(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
DEMO
Here is the visual demo for rank no for each row for better understanding
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 ofCASE
statement likeDEMO
Above query will pick the row with highest id among the same
datetime
valuesvisual 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.
@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:
@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:
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 andbefore
and I highly doubt it to behave otherwise in MySQL version 5.7 andafter
.@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.
Or in case of Postgres or those dbs that provide analytic functions try
@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 Version with User variable
Accepted Answers' outout
@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:
@Jr. 2012-07-12 16:11:16
@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
@Quassnoi 2009-03-04 20:24:20
This will work even if you have two or more rows for each
home
with equalDATETIME
's:@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 lineWHERE ti.home = t1.home
does not need theFROM
clause that definest1
. So how is it used ?@Quassnoi 2017-11-08 18:18:00
@IstiaqueAhmed: dev.mysql.com/doc/refman/5.7/en/correlated-subqueries.html
@Maksym Gontar 2009-03-04 20:59:52
Here goes T-SQL version:
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:
@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:
@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
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)
isdatetime
. Won't it make any problem ?@Istiaque Ahmed 2017-11-08 13:04:25
How is the highest
datetime
selected ?