List:General Discussion« Previous MessageNext Message »
From:dan Date:January 20 2011 8:08am
Subject:Re: Group by question
View as plain text  
On 2011-1-16 20:22, Jørn Dahl-Stamnes wrote:
> Hello,
>
> I got a table that store information about which photo-albums that a client is
> viewing. I want to get the N last visited albums and use the query:
>
> mysql>  select album_id, updated_at, created_at from album_stats order by
> updated_at desc limit 8;
> +----------+---------------------+---------------------+
> | album_id | updated_at          | created_at          |
> +----------+---------------------+---------------------+
> |       51 | 2011-01-16 13:03:04 | 2011-01-16 13:03:04 |
> |       10 | 2011-01-16 12:20:39 | 2011-01-16 12:20:39 |
> |        2 | 2011-01-16 12:20:35 | 2011-01-16 12:20:35 |
> |       81 | 2011-01-16 12:20:34 | 2011-01-16 12:20:34 |
> |       97 | 2011-01-16 11:25:03 | 2011-01-16 11:19:05 |
> |       81 | 2011-01-16 11:19:04 | 2011-01-16 11:19:04 |
> |        2 | 2011-01-16 11:19:02 | 2011-01-16 11:19:02 |
> |       10 | 2011-01-16 11:18:58 | 2011-01-16 11:13:04 |
> +----------+---------------------+---------------------+
> 8 rows in set (0.09 sec)
>
>
> The problem is that album_id 81 is occuring two times in the list. So I
> thought I should add a "group by" in the query:
>
> mysql>  select album_id, updated_at, created_at from album_stats group by
> album_id order by updated_at desc limit 8;
> +----------+---------------------+---------------------+
> | album_id | updated_at          | created_at          |
> +----------+---------------------+---------------------+
> |      278 | 2011-01-13 14:02:50 | 2011-01-13 14:02:00 |
> |      281 | 2011-01-11 16:41:16 | 2011-01-11 16:35:41 |
> |      276 | 2010-12-15 14:42:57 | 2010-12-15 14:42:57 |
> |      275 | 2010-12-15 14:42:48 | 2010-12-15 14:42:48 |
> |      269 | 2010-09-11 14:09:10 | 2010-09-11 14:09:10 |
> |      271 | 2010-09-11 14:02:27 | 2010-09-11 14:02:27 |
> |      273 | 2010-09-11 13:59:06 | 2010-09-11 13:59:06 |
> |      270 | 2010-09-11 13:57:25 | 2010-09-11 13:57:25 |
> +----------+---------------------+---------------------+
> 8 rows in set (0.23 sec)
>
> But the result is not what I expected. What have I missed?
>
perhaps i think first you need to retrieve the max(updated_at) group by 
album_id sets

select album_id, updated_at, created_at from album_stats where 
updated_at in (select max(updated_at) from album_stats group by 
album_id) limit 8;

Thread
Group by questionJørn Dahl-Stamnes16 Jan
  • Re: Group by questionSteve Meyers17 Jan
    • Re: Group by questionJørn Dahl-Stamnes17 Jan
      • Re: Group by questionLuciano Furtado17 Jan
  • Re: Group by questiondan20 Jan