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=arch...@jab.org
