0

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.

Community
  • 1
  • 1
aaronwinborn
  • 1
  • 1

1 Answers1

0
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.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • that doesn't seem to preserve my need to order by 'time'. – aaronwinborn Apr 19 '11 at 20:11
  • So you want the LAST record in any of the groups? – Marc B Apr 19 '11 at 20:14
  • yes, that's what i want! i'd want the record matching the last time, grouped by otheridentifier, with a count of all in that particular group. – aaronwinborn Apr 19 '11 at 20:30
  • assuming the ids are an auto-increment column, then simplly select `MAX(id)` instead of `*`, which'd give you the highest ID within each group. – Marc B Apr 19 '11 at 20:31
  • no, sorry, in my case id is actually a reference from another table. even with this hypothetical example, it works with this configuration, but wouldn't work if 'time' were more random than how it's presented. – aaronwinborn Apr 19 '11 at 20:41