How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL? - Printable Version +- 0Day Forums (https://0day.red) +-- Forum: Coding (https://0day.red/Forum-Coding) +--- Forum: Database (https://0day.red/Forum-Database) +---- Forum: MySQL (https://0day.red/Forum-MySQL) +---- Thread: How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL? (/Thread-How-can-I-SELECT-rows-with-MAX-Column-value-PARTITION-by-another-column-in-MYSQL) |
How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL? - explain856316 - 07-27-2023 I have a table of player performance: ``` CREATE TABLE TopTen ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, home INT UNSIGNED NOT NULL, `datetime`DATETIME NOT NULL, player VARCHAR(6) NOT NULL, resource INT NOT NULL ); ``` What query will return the rows for each distinct `home` holding its maximum value of `datetime`? In other words, how can I filter by the maximum `datetime` (grouped by `home`) and still include other non-grouped, non-aggregate columns (such as `player`) in the result? For this sample data: ``` INSERT INTO TopTen (id, home, `datetime`, player, resource) VALUES (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) ; ``` the result should 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 tried a subquery getting the maximum `datetime` for each `home`: -- 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 The result-set has 130 rows although database holds 187, indicating the result includes some duplicates of `home`. Then I tried joining to a subquery that gets the maximum `datetime` for each row `id`: -- 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. I tried various exotic queries, each with various results, but nothing that got me any closer to solving this problem. RE: How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL? - fiddlestring453752 - 07-27-2023 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 RE: How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL? - deibel963 - 07-27-2023 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 RE: How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL? - didosgfqga - 07-27-2023 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][1]. 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 [1]: [To see links please register here] RE: How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL? - faddists723409 - 07-27-2023 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 RE: How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL? - gumboots739535 - 07-27-2023 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 RE: How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL? - terrenceterrene727 - 07-27-2023 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. RE: How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL? - nyala397822 - 07-27-2023 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) RE: How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL? - lyraibiuwknk - 07-27-2023 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 RE: How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL? - Mrsaiedza - 07-27-2023 Here is MySQL version which prints only one entry where there are duplicates MAX(datetime) in a group. You could test here [To see links please register here] ##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) |