1

Given the schema

enter image description here

The following query

SELECT a.user_id,
  a.date_created,
  avg(ai.level) level
FROM assessment a
  JOIN assessment_item ai ON a.id = ai.assessment_id
GROUP BY a.user_id, a.date_created;

Returns these results

user_id, a.date_created,        level
1,       "2015-07-13 18:26:00", 4.0000  
1,       "2015-07-13 19:04:58", 6.0000  
13,      "2015-07-13 18:26:00", 2.0000  
13,      "2015-07-13 19:04:58", 3.0000

I would like to change the query such that only the earliest result is returned for each user. In other words, the following should be returned instead

user_id, a.date_created,        level
1,       "2015-07-13 18:26:00", 4.0000
13,      "2015-07-13 18:26:00", 2.0000
Antonio Dragos
  • 1,973
  • 2
  • 29
  • 52
  • In general, if you select something, then you have to group by it too. MySQL forgives the 'error'. Most other RDBMSs do not. – Strawberry Jul 13 '15 at 21:56
  • If you're trying to average ai.level, shouldn't the results for user_id be 5 and 2.5? – Acantud Jul 13 '15 at 22:04
  • possible duplicate of [SELECT query return 1 row from each group](http://stackoverflow.com/questions/14375099/select-query-return-1-row-from-each-group) – Barranka Jul 13 '15 at 22:25
  • possible duplicate of [Using LIMIT within GROUP BY to get N results per group?](http://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) – PM 77-1 Jul 13 '15 at 22:46

1 Answers1

1

A possible solution: use user variables to 'index' the rows, and then filter the first row:

select user_id, date_created, level
from (
    select a.date_created, a.level
         , @idx := (case when a.user_id = @uid then @idx + 1 else 1 end) as idx
         , @uid := a.user_id as user_id
    from 
        (select @uid := 0, @idx := 0) as init,
        (
            SELECT a.user_id,
              a.date_created,
              avg(ai.level) level
            FROM assessment a
              JOIN assessment_item ai ON a.id = ai.assessment_id
            GROUP BY a.user_id, a.id
            order by user_id, date_created
        ) as a
) as b
where idx = 1
Barranka
  • 20,547
  • 13
  • 65
  • 83
  • that's very impressive, but I'm surprised that something which seems relatively simple should require such a complex solution – Antonio Dragos Jul 13 '15 at 22:07
  • @AntonioDragos A useful reference: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ – Barranka Jul 13 '15 at 23:01