Group by question

Hello,

I got a table that store information about which photo-albums that a client=
is=20
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=20
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=20
thought I should add a "group by" in the query:

mysql> select album_id, updated_at, created_at from album_stats group by=20
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?

=2D-=20
J=F8rn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Group by question

On 1/16/11 5:22 AM, Jørn Dahl-Stamnes wrote:
> mysql> select album_id, updated_at, created_at from album_stats group by
> album_id order by updated_at desc limit 8;

I believe that your problem is that the group by happens before the
order by. Since you're grouping, the updated_at column is not
deterministic. If there are multiple rows per album_id, any one of
those rows could provide the updated_at column that you're then using to
order by. What you probably want is to select (and order by) the
max(updated_at).

Steve

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Group by question

On Monday 17 January 2011 09:53, Steve Meyers wrote:
> On 1/16/11 5:22 AM, J=F8rn Dahl-Stamnes wrote:
> > mysql> select album_id, updated_at, created_at from album_stats group =
by
> > album_id order by updated_at desc limit 8;
>
> I believe that your problem is that the group by happens before the
> order by. Since you're grouping, the updated_at column is not
> deterministic. If there are multiple rows per album_id, any one of
> those rows could provide the updated_at column that you're then using to
> order by. What you probably want is to select (and order by) the
> max(updated_at).

moving the "group by" to after "order by" will result in an error:

mysql> select album_id, updated_at, created_at from album_stats order by=20
updated_at group by album_id desc limit 8;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual=
=20
that corresponds to your MySQL server version for the right syntax to use=20
near 'group by album_id desc limit 8' at line 1

Beside, since I'm using Propel and Creole for ORM and database abstraction,=
I=20
would never be able to change the order of them.

=2D-=20
J=F8rn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Group by question

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

He meant the execution order, please use the agregation function as
suggested.

On 11-01-17 05:03, Jørn Dahl-Stamnes wrote:
> On Monday 17 January 2011 09:53, Steve Meyers wrote:
>> On 1/16/11 5:22 AM, Jørn Dahl-Stamnes wrote:
>>> mysql> select album_id, updated_at, created_at from album_stats group by
>>> album_id order by updated_at desc limit 8;
>>
>> I believe that your problem is that the group by happens before the
>> order by. Since you're grouping, the updated_at column is not
>> deterministic. If there are multiple rows per album_id, any one of
>> those rows could provide the updated_at column that you're then using to
>> order by. What you probably want is to select (and order by) the
>> max(updated_at).
>
> moving the "group by" to after "order by" will result in an error:
>
> mysql> select album_id, updated_at, created_at from album_stats order by
> updated_at group by album_id desc limit 8;
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to use
> near 'group by album_id desc limit 8' at line 1
>
> Beside, since I'm using Propel and Creole for ORM and database abstraction, I
> would never be able to change the order of them.
>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG/MacGPG2 v2.0.14 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQEcBAEBAgAGBQJNNHQIAAoJENgwSj9ZOOwrR9UH/RDLojUwXYB1a+fcS0ku wzFW
AZ/VdwknydB5ENkt7+MmWgHdVcPwrChE+nY2bpRI9LYp8ehUNwaeC2uV/ToW XFti
ZTnVdnFDanHy20UOF3a1X7UXW89/zTy/B06X7NP1NqmIGnAahPK6VBuIx1OP /oGZ
+es+m9BIYnuc8JzfRo5YSQuydfWIJ87ygrkodhM/C2VPBWDMwpEX/wuxgW/x +ukM
RXKaxHrHOrWc1hWLFp3P+QI+J7VNP1fh6Rxw1Q91latJkY4I3hbN9nEsXeHl D2l2
f5ZNn4LwPPNC++XpGaDqQmA0W2Sua9lHUhtVsxsJt5kIigAwNJ5GnaayY/p9 apk=
=7MLg
-----END PGP SIGNATURE-----


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Group by question

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;


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org