How can I SELECT rows with Max value, DISTINCT by another column in MYSQL

Question

I have this example table:

id        city       show    
-------  -------    ------ 
 1        Paris     Show1
 2        NY        Show1
 3        Paris     Show2
 4        Madrid    Show3
 5        Madrid    Show3
 6        NY        Show2
 7        NY        Show1
 8        Paris     Show2

Plz can Anyone help me with a MySQL query to get the most seen show by city. The result should be like this:

city      Show      Occurence
 NY       Show1        2
Paris     Show2        2
Madrid    Show3        2

I will be very grateful for your help.

SELECT city, show, count(show) FROM table GROUP BY city – Israel Merljak Jan 17 '18 at 17:04 — Israel Merljak, Jan 17 '18 at 17:04

score 1 · Answer 1 · answered Jan 17 '18 at 17:16

E.g.:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,city VARCHAR(12) NOT NULL
,performance VARCHAR(12) NOT NULL
);

INSERT INTO my_table VALUES
(1,'Paris'     ,'Show1'),
(2,'NY'        ,'Show1'),
(3,'Paris'     ,'Show2'),
(4,'Madrid'    ,'Show3'),
(5,'Madrid'    ,'Show3'),
(6,'NY'        ,'Show2'),
(7,'NY'        ,'Show1'),
(8,'Paris'     ,'Show2');   


 SELECT x.*
      , z.total
   FROM my_table x
   JOIN
      ( SELECT city, MAX(id) max_id FROM my_table GROUP BY city ) y
     ON y.city = x.city
    AND y.max_id = id
   JOIN (SELECT city, performance, COUNT(*) total FROM my_table GROUP BY city,performance) z
     ON z.city = x.city 
    AND z.performance = x.performance;

+----+--------+-------------+-------+
| id | city   | performance | total |
+----+--------+-------------+-------+
|  5 | Madrid | Show3       |     2 |
|  7 | NY     | Show1       |     2 |
|  8 | Paris  | Show2       |     2 |
+----+--------+-------------+-------+

dognose · Accepted Answer · 2018-01-17T17:49:38.927

1

There's a super-simple way to do this in mysql:

SELECT * FROM (
    SELECT 
       city, 
       `show`, 
       count(id)  
    FROM 
       showTable 
    GROUP BY 
       city,`show` 
    ORDER BY 
       count(id) 
    DESC
) as temp 
GROUP BY 
  city

This works because in mysql you're allowed to not aggregate non-group-by columns, in which case mysql just returns the first row. The solution is to first order the data such that for each group the row you want is first, then group by the columns you want the value for (in this case city).

Note, that ordering the INNER query is very important. Else the order is undetermined, so using GROUP BY with no aggregation (on the outer query) will from time to time return another row. (You will also face this, if two shows have an equal count in the same city - sometimes it will return "showX", sometimes "showY")

http://sqlfiddle.com/#!9/0b887/7

(After testing the query, I found a well written statement here, basically same question - so no need to write my own: Get records with max value for each group of grouped SQL results )

ps.: If you check this fiddle: http://sqlfiddle.com/#!9/7a9be9/1 - TESTTOWN will from Time to Time alternate between show1 and show2.

edited Jan 17 '18 at 17:49

answered Jan 17 '18 at 17:22

dognose

20,360
9
61
107

But note that this is called an (undocumented) 'hack' – Strawberry Jan 17 '18 at 17:25
The order is indeterminate regardless. The current version (and all previous versions) of MySQL just happens to get it right, but that may all change in version 11!! – Strawberry Jan 17 '18 at 17:39
I'm with Thorsten Kettner on this one. – Strawberry Jan 17 '18 at 17:40
@Strawberry you may be right - or not. We'll never know cause it's undocumented as you said. Well, if you want to play it save, your query will do it. Ofc. For a high-grade business application one might not use something "undocumented", as the next mysql-version could break it. But there's a huge amount of websites, that never upgrade their database, once it was setup. – dognose Jan 17 '18 at 17:43
If it was faster, then I'd concede the point, but it's not, so I just can't see why you'd opt for this method over one of the documented methods. – Strawberry Jan 17 '18 at 17:49
@Strawberry testet your query, for a few tests it's 3ms vs 10ms compared to your version (ofc, would need to do this with a few million records). Your Version has also a "flaw": http://sqlfiddle.com/#!9/edc75e/1 It will always report "Show2" for testtown as far as I can tell, cause it will "win" the `max_id` constraint, just because the "last-view" was Show2. – dognose Jan 17 '18 at 17:54
I don't think that's a flaw. I understood that was what was wanted - but I may have misunderstood. It's possible that your query outperforms mine, but rest assured we can find a documented method that outperforms yours ;-) – Strawberry Jan 17 '18 at 18:02
@Strawberry I ran both queries against 250.000 Records. Each Query 50 times. Well, unindexed, both of them suck, but it's like: "Your Version: `4.0` / My Version: `0.58` " here's a php code, if you wanna play arround to find a "documented method, that outperforms mine!" ;-) https://pastebin.com/j80F0kEk / https://abload.de/img/captureh5urs.jpg – dognose Jan 17 '18 at 19:03

How can I SELECT rows with Max value, DISTINCT by another column in MYSQL

2 Answers2