GROUP BY - sorting pre and post grouping

Question

I am HAVING trouble with my GROUP BY query (the trickiest of them all at least for me).

What I want to do is retrieve the most recent modified records grouped by another column. The problem as I see it is that grouping returns the only first record it finds (in the group) disregarding the ORDER BY (except when returning the final result).

Here is a simplified version of my code.

SELECT events.id, events.name, events.type, events.modified_time
FROM events
GROUP BY events.type
ORDER BY event.modified_time DESC

Which will return:

1 | Event One   | Birthday   | Jan 1, 2012  
2 | Event Two   | Graduation | Jan 1, 2012

When in fact there is a 3rd record with a modified time that is later:

3 | Event Three | Birthday   | Jan 2, 2012

I've tried using a HAVING clause of MAX(modified_time) but it doesn't return the correct results either. Maybe I just need to discuss this more with someone who is knowledgeable but if the question makes enough sense and you can tell what I need then maybe there is an easy answer.

BONUS QUESTION:
Is it possible to do this without a sub query?

Can't you just add `events.modified_time` to the GROUP BY clause? — Lieven Keersmaekers, Jan 04 '12 at 22:49
@Lieven Then I will get 3 records when I really want the 2 latest. — Matthew, Jan 04 '12 at 22:50
ORDER BY will not affect the GROUP BY, if you will, but rather only the final sorting. — Tim Lehner, Jan 04 '12 at 22:51
I answered something similar earlier today: http://stackoverflow.com/questions/8729333/sql-return-only-first-occurrence/8729392 — Tim Lehner, Jan 04 '12 at 22:53
@Tim Lehner Thanks. So, a sub-query then. I suspected as much. GROUP BY breaks my brain sometimes but I try to use it where applicable if I can. Is using a group by just plain wrong to do in my scenario? — Matthew, Jan 04 '12 at 23:00
@Matthew, sorry, I misread. In that case, changing the `events.modified_time` to `MAX(events.modified_time)` without using a having clause should do the trick. — Lieven Keersmaekers, Jan 04 '12 at 23:02
http://stackoverflow.com/questions/tagged/greatest-n-per-group+mysql — kurosch, Jan 04 '12 at 23:27
Please note that this only 'works' because it's MySQL - this won't run on most RDBMSs. Specifically, either more columns need to be added to the `GROUP BY`, or columns need to have aggregate functions (`SUM()`, `MAX()`, etc) applied. MySQL gives you (sorta) _random_ values for the other columns that aren't properly handled (which is **stupid** - you're getting what is potentially an _invalid_ result, and it doesn't even tell you). — Clockwork-Muse, Jan 04 '12 at 23:38

score 1 · Answer 1 · answered Jan 04 '12 at 22:57

1

GROUP BY/HAVING is not what you want. What you want is either:

WHERE
    events.modified_time = ( select max(modified_time) from events e2 where e2.type = events.type )

... or else a solution like Tim linked to using an analytic function like ROW_NUMBER

answered Jan 04 '12 at 22:57

kurosch

2,292
16
17

Note that if you have more than one event of the same type with the same modified_time, a condition like this will return all of them. – kurosch Jan 04 '12 at 23:03
1

AFAIK, MySQL does not have a similar concept like SQL Server's ROW_NUMBER. – Lieven Keersmaekers Jan 04 '12 at 23:04
1

Note that `ROW_NUMBER` doesn't exist in MySQL. – Michael Mior Jan 04 '12 at 23:05
@Lieven: just a minor nitpick: It's not "SQL Server's" row_number(). That's called a windowing function (standard ANSI SQL) and Oracle and others have it since ages - and much more powerful as well. – Jan 04 '12 at 23:10
@a_horse_with_no_name - I know but the mentioned link was about SQL Server hence my reference to it. – Lieven Keersmaekers Jan 04 '12 at 23:12
Surely there is some analogous feature to ROW_NUMBER? – kurosch Jan 04 '12 at 23:12
Then what OP really needs is a real database :-P – kurosch Jan 04 '12 at 23:14
Where "real" in this case is defined strictly as "contains windowing functions", and not, for example, "is used to power countless real-world systems". :P – Dan J Jan 05 '12 at 00:38

score 1 · Accepted Answer · answered Jan 04 '12 at 22:59

1

This, among many other methods, may work for you:

SELECT a.id, a.name, a.[type], a.modified_time
FROM [events] AS a
    JOIN (
        SELECT MAX([events].id) AS id, [events].[type]
        FROM [events]
        GROUP BY [events].[type]    
    ) AS b ON a.id = b.id AND a.[type] = b.[type]
ORDER BY a.modified_time DESC

answered Jan 04 '12 at 22:59

Tim Lehner

14,813
4
59
76

You're assuming a larger id always corresponds to a later modified_time. – kurosch Jan 04 '12 at 23:01
lol...the linked solution that I have shows the other way, too. We'll see what the OP wants, I guess! – Tim Lehner Jan 04 '12 at 23:04
@TimLehner Since the OP was grouping by `type` and asking for the record with the most recent `modified_time`, it's valid. – Michael Mior Jan 04 '12 at 23:06
@Michael Mior I would still say that both assumptions are potential pitfalls that only the person with the data could evaluate. – Tim Lehner Jan 04 '12 at 23:09
@kurosch Thanks for pre-emptively pointing out things I would have. Also, yes, the id is the key and modified_time can be changed later. – Matthew Jan 04 '12 at 23:11

GROUP BY - sorting pre and post grouping

2 Answers2