MYSQL - Group by limit

Question

Is there a simple way to LIMIT the GROUP BY results to the top 2. The following query returns all the results. Using 'LIMIT 2' reduces the overall list to the top 2 entries only.

select distinct(rating_name), 
       id_markets, 
       sum(rating_good) 'good', 
       sum(rating_neutral)'neutral', 
       sum(rating_bad) 'bad' 
 from ratings 
 where rating_year=year(curdate()) and rating_week= week(curdate(),1)
 group by rating_name,id_markets
 order by rating_name, sum(rating_good) 
 desc

Results in the following :-

poland  78 48 24 12   <- keep
poland   1 15  5  0   <- keep
poland  23 12  6  3
poland   2  5  0  0
poland   3  0  5  0
poland   4  0  0  5
ireland  1  9  3  0   <- keep
ireland  2  3  0  0   <- keep
ireland  3  0  3  0
ireland  4  0  0  3
france  12 24 12  6   <- keep
france   1  3  1  0   <- keep
france 231  1  0  0
france   2  1  0  0
france   4  0  0  1
france   3  0  1  0

Thanks Jon

As requested I have attached a copy of the table structure and some test data. My goal is to create a single view that has the top 2 results from each unique rating_name

CREATE TABLE `zzratings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_markets` int(11) DEFAULT NULL,
  `id_account` int(11) DEFAULT NULL,
  `id_users` int(11) DEFAULT NULL,
  `dateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `rating_good` int(11) DEFAULT NULL,
  `rating_neutral` int(11) DEFAULT NULL,
  `rating_bad` int(11) DEFAULT NULL,
  `rating_name` varchar(32) DEFAULT NULL,
  `rating_year` smallint(4) DEFAULT NULL,
  `rating_week` tinyint(4) DEFAULT NULL,
  `cash_balance` decimal(9,6) DEFAULT NULL,
  `cash_spend` decimal(9,6) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `rating_year` (`rating_year`),
  KEY `rating_week` (`rating_week`),
  KEY `rating_name` (`rating_name`)
) ENGINE=MyISAM AUTO_INCREMENT=2166690 DEFAULT CHARSET=latin1;

INSERT INTO `zzratings` (`id`,`id_markets`,`id_account`,`id_users`,`dateTime`,`rating_good`,`rating_neutral`,`rating_bad`,`rating_name`,`rating_year`,`rating_week`,`cash_balance`,`cash_spend`)
VALUES
    (63741, 1, NULL, 100, NULL, 1, NULL, NULL, 'poland', 2010, 15, NULL, NULL),
    (63742, 1, NULL, 101, NULL, 1, NULL, NULL, 'poland', 2010, 15, NULL, NULL),
    (1, 2, NULL, 102, NULL, 1, NULL, NULL, 'poland', 2010, 15, NULL, NULL),
    (63743, 3, NULL, 103, NULL, NULL, 1, NULL, 'poland', 2010, 15, NULL, NULL),
    (63744, 4, NULL, 104, NULL, NULL, NULL, 1, 'poland', 2010, 15, NULL, NULL),
    (63745, 1, NULL, 105, NULL, 1, NULL, NULL, 'poland', 2010, 15, NULL, NULL),
    (63746, 1, NULL, 106, NULL, NULL, 1, NULL, 'poland', 2010, 15, NULL, NULL),
    (63747, 5, NULL, 100, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63748, 5, NULL, 101, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63749, 2, NULL, 102, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63750, 3, NULL, 103, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63751, 4, NULL, 104, NULL, NULL, NULL, 1, 'ireland', 2010, 15, NULL, NULL),
    (63752, 1, NULL, 105, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63753, 1, NULL, 106, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63754, 1, NULL, 100, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63755, 1, NULL, 101, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63756, 2, NULL, 102, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63757, 34, NULL, 103, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63758, 34, NULL, 104, NULL, NULL, NULL, 1, 'ireland', 2010, 15, NULL, NULL),
    (63759, 34, NULL, 105, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63760, 34, NULL, 106, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63761, 21, NULL, 100, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63762, 21, NULL, 101, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63763, 21, NULL, 102, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63764, 21, NULL, 103, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63765, 4, NULL, 104, NULL, NULL, NULL, 1, 'ireland', 2010, 15, NULL, NULL),
    (63766, 1, NULL, 105, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63767, 1, NULL, 106, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
    (63768, 1, NULL, 100, NULL, 1, NULL, NULL, 'france', 2010, 15, NULL, NULL),
    (63769, 1, NULL, 101, NULL, 1, NULL, NULL, 'france', 2010, 15, NULL, NULL),
    (63770, 2, NULL, 102, NULL, 1, NULL, NULL, 'france', 2010, 15, NULL, NULL),
    (63771, 3, NULL, 103, NULL, NULL, 1, NULL, 'france', 2010, 15, NULL, NULL),
    (63772, 4, NULL, 104, NULL, NULL, NULL, 1, 'france', 2010, 15, NULL, NULL);

Since `GROUP BY` is for performing aggregate functions on related data, it's unlikely. MySQL doesn't see 6 Poland, 4 Ireland and 6 France groups, it sees 16 distinct groups with nothing relating them. What are you trying to achieve? Perhaps there is another way to do the grouping. — Duncan, Apr 15 '10 at 07:27
Could you provide table structure and some test data? I think there is a possibility to do this using HAVING and subquery there. — Draco Ater, Apr 15 '10 at 07:39

Mark Byers · Accepted Answer · 2010-04-16T05:40:42.150

I don't think that there is a simple way in MySQL. One way to do this is by generating a row number for each row partitioned in groups by rating_name, and then only select the rows with row_number 2 or less. In most databases you could do this using something like:

SELECT * FROM (
    SELECT
        rating_name,
        etc...,
        ROW_NUMBER() OVER (PARTITION BY rating_name ORDER BY good) AS rn
    FROM your_table
) T1
WHERE rn <= 2

Unfortunately, MySQL doesn't support the ROW_NUMBER syntax. You can however simulate ROW_NUMBER using variables:

SELECT
    rating_name, id_markets, good, neutral, bad
FROM (
    SELECT
        *,
        @rn := CASE WHEN @prev_rating_name = rating_name THEN @rn + 1 ELSE 1 END AS rn,
        @prev_rating_name := rating_name
    FROM (
        SELECT
            rating_name,
            id_markets,
            SUM(COALESCE(rating_good, 0)) AS good,
            SUM(COALESCE(rating_neutral, 0)) AS neutral,
            SUM(COALESCE(rating_bad, 0)) AS bad
        FROM zzratings
        WHERE rating_year = YEAR(CURDATE()) AND rating_week = WEEK(CURDATE(), 1)
        GROUP BY rating_name, id_markets
    ) AS T1, (SELECT @prev_rating_name := '', @rn := 0) AS vars
    ORDER BY rating_name, good DESC
) AS T2
WHERE rn <= 2
ORDER BY rating_name, good DESC

Result when run on your test data:

france    1  2  0  0
france    2  1  0  0
ireland   1  4  2  0
ireland  21  3  1  0
poland    1  3  1  0
poland    2  1  0  0

Hi Draco - I have updated the post to include the table structure and the data. I appreciate all the feedback so far - thank you. — jono2010, Apr 16 '10 at 00:43

Marcus Adams · Answer 2 · 2010-04-16T02:08:34.997

This is still possible via a single query, but it's a bit long, and there are some caveats, which I'll explain after the query. Though, they're not flaws in the query so much as some ambiguity in what "top two" means.

Here's the query:

SELECT ratings.* FROM
(SELECT rating_name, 
       id_markets, 
       sum(rating_good) 'good', 
       sum(rating_neutral)'neutral', 
       sum(rating_bad) 'bad' 
 FROM zzratings 
 WHERE rating_year=year(curdate()) AND rating_week = week(curdate(),1)
 GROUP BY rating_name,id_markets) AS ratings
LEFT JOIN
(SELECT rating_name, 
       id_markets, 
       sum(rating_good) 'good', 
       sum(rating_neutral)'neutral', 
       sum(rating_bad) 'bad' 
 FROM zzratings 
 WHERE rating_year=year(curdate()) AND rating_week= week(curdate(),1)
 GROUP BY rating_name,id_markets) AS ratings2
ON ratings2.good <= ratings.good AND
  ratings2.id_markets <> ratings.id_markets AND
  ratings2.rating_name = ratings.rating_name
LEFT JOIN
(SELECT rating_name, 
       id_markets, 
       sum(rating_good) 'good', 
       sum(rating_neutral)'neutral', 
       sum(rating_bad) 'bad' 
 FROM zzratings 
 WHERE rating_year=year(curdate()) AND rating_week= week(curdate(),1)
 GROUP BY rating_name,id_markets) AS ratings3
ON ratings3.good >= ratings2.good AND
  ratings3.id_markets <> ratings.id_markets AND
  ratings3.id_markets <> ratings2.id_markets AND
  ratings3.rating_name = ratings.rating_name
WHERE (ratings2.good IS NULL OR ratings3.good IS NULL) AND
  ratings.good IS NOT NULL
ORDER BY ratings.rating_name, ratings.good DESC

The caveat is that if there is more than one id_market with the same "good" count for the same rating_name, then you will get more than two records. For example, if there are three ireland id_markets with a "good" count of 3, the highest, then how can you display the top two? You can't. So the query will show all three.

Also, if there were one count of "3", the highest, and two counts of "2", you couldn't show the top two, since you have a tie for second place, so the query shows all three.

The query will be simpler if you create a temporary table with the aggregate result set first, then work from that.

CREATE TEMPORARY TABLE temp_table
  SELECT rating_name, 
           id_markets, 
           sum(rating_good) 'good', 
           sum(rating_neutral)'neutral', 
           sum(rating_bad) 'bad' 
     FROM zzratings 
     WHERE rating_year=year(curdate()) AND rating_week= week(curdate(),1;

SELECT ratings.*
 FROM temp_table ratings
LEFT JOIN temp_table ratings2
ON ratings2.good <= ratings.good AND
  ratings2.id_markets <> ratings.id_markets AND
  ratings2.rating_name = ratings.rating_name
LEFT JOIN temp_table ratings3
ON ratings3.good >= ratings2.good AND
  ratings3.id_markets <> ratings.id_markets AND
  ratings3.id_markets <> ratings2.id_markets AND
  ratings3.rating_name = ratings.rating_name
WHERE (ratings2.good IS NULL OR ratings3.good IS NULL) AND
  ratings.good IS NOT NULL
ORDER BY ratings.rating_name, ratings.good DESC;

Afternoon all, MANY thanks the highly considered feedback - it is much appreciated! Regards Jon — jono2010, Apr 19 '10 at 04:34

score 0 · Answer 3 · answered Nov 16 '16 at 07:51

0

SUBSTRING_INDEX(
    GROUP_CONCAT(expr1 ORDER BY expr2 SEPARATOR ";"),
    ";",
    2  /* the GROUP_LIMIT */
)

expr1 can be like CONCAT(...). Involve REPLACE to hide any ";".

answered Nov 16 '16 at 07:51

Ruud H.G. van Tol

1
1

MYSQL - Group by limit

3 Answers3

Linked