CODEWITHSUNDEEP
mysqlgroup-bymysql-error-1140
aaronwinborn
aaronwinborn

Reputation: 1

MYSQL Group Gy ordering with Count?

I'm trying to do something similar to Select the 3 most recent records where the values of one column are distinct, but with a count, which means I need to preserve the group.

Using the same example:

 id       time      text      otheridentifier
-------------------------------------------
1        6         apple     4
2        7         orange    4
3        8         banana    3
4        9         pear      3
5        10        grape     2

SELECT *, COUNT(*) FROM `table` GROUP BY (`otheridentifier`) ORDER BY `time` DESC LIMIT 3

would return id of 5, 3, 1 with the proper counts, but I want 5, 4, 2.

The solution in that post was 

SELECT * FROM 'table' WHERE 'id' = (SELECT 'id'
FROM 'table' as 'alt'
WHERE 'alt'.'otheridentifier' = 'table'.'otheridentifier'
ORDER BY 'time' DESC
LIMIT 1) ORDER BY 'time' DESC LIMIT 3

but I don't see how to grab a count, since I need a grouping there: I get ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause if I add COUNT(*) to the beginning of that.

Upvotes: 0

Views: 783

Answers (1)

Marc B
Marc B

Reputation: 360762

SELECT *, COUNT(*)
FROM `table`
GROUP BY (`otheridentifier`)
ORDER BY COUNT(*) DESC LIMIT 3
^^^^^^^^^^^^^^^^^

simply change the order-by clause. ordering is done after the grouping/aggregating is completed, so you can sort by the results of those aggregate functions.

Upvotes: 0

Related Questions