I have a MySQl suggestion table like this
id:int
user_id:int
item_id:int
and I wanted to implement paging for the most suggested items by users. assume each page size is 9. so i have tried
select count(id) as total,item_id from suggestions group by item_id order by total desc limit 9;
which simply works for the first page and returns
+-------+---------+
| total | item_id |
+-------+---------+
| 6 | 4 |
| 6 | 8 |
| 6 | 16 |
| 5 | 5 |
| 5 | 6 |
| 5 | 1 |
| 3 | 17 |
| 2 | 3 |
| 2 | 7 |
+-------+---------+
in second page i wanted to show the results from total = 2 and item_id =2. In SQL server i can use Row_number() method to number the rows for paging. But i can't find something like this in MySQL.
am new to mysql, can somebody help me out.
see the attached the sample data below
+----+---------+---------+---------------------+---------------------+
| id | user_id | item_id | created_at | updated_at |
+----+---------+---------+---------------------+---------------------+
| 1 | 4 | 1 | 2011-04-18 05:21:08 | 2011-04-18 05:21:08 |
| 6 | 5 | 2 | 2011-04-18 07:28:25 | 2011-04-18 07:28:25 |
| 8 | 5 | 3 | 2011-04-18 08:10:46 | 2011-04-18 08:10:46 |
| 9 | 4 | 4 | 2011-04-18 12:58:17 | 2011-04-18 12:58:17 |
| 10 | 4 | 5 | 2011-04-18 13:24:59 | 2011-04-18 13:24:59 |
| 11 | 4 | 6 | 2011-04-18 13:28:28 | 2011-04-18 13:28:28 |
| 12 | 4 | 7 | 2011-04-18 13:30:20 | 2011-04-18 13:30:20 |
| 13 | 8 | 5 | 2011-04-18 13:42:30 | 2011-04-18 13:42:30 |
| 14 | 8 | 6 | 2011-04-18 13:42:44 | 2011-04-18 13:42:44 |
| 15 | 8 | 4 | 2011-04-18 13:42:52 | 2011-04-18 13:42:52 |
| 16 | 9 | 5 | 2011-04-18 13:44:34 | 2011-04-18 13:44:34 |
| 17 | 9 | 6 | 2011-04-18 13:45:23 | 2011-04-18 13:45:23 |
| 18 | 9 | 7 | 2011-04-18 13:46:28 | 2011-04-18 13:46:28 |
| 19 | 9 | 1 | 2011-04-18 13:46:29 | 2011-04-18 13:46:29 |
| 20 | 4 | 8 | 2011-04-18 13:58:38 | 2011-04-18 13:58:38 |
| 21 | 8 | 8 | 2011-04-18 13:59:39 | 2011-04-18 13:59:39 |
| 22 | 9 | 8 | 2011-04-18 14:00:47 | 2011-04-18 14:00:47 |
| 23 | 8 | 1 | 2011-04-18 14:03:58 | 2011-04-18 14:03:58 |
| 24 | 4 | 9 | 2011-04-19 10:24:38 | 2011-04-19 10:24:38 |
| 25 | 4 | 10 | 2011-04-19 10:54:13 | 2011-04-19 10:54:13 |
| 26 | 4 | 11 | 2011-04-19 10:57:10 | 2011-04-19 10:57:10 |
| 27 | 12 | 8 | 2011-04-19 11:33:02 | 2011-04-19 11:33:02 |
| 28 | 12 | 4 | 2011-04-19 11:33:18 | 2011-04-19 11:33:18 |
| 29 | 12 | 1 | 2011-04-19 12:07:51 | 2011-04-19 12:07:51 |
| 30 | 12 | 6 | 2011-04-19 12:09:05 | 2011-04-19 12:09:05 |
| 31 | 12 | 12 | 2011-04-19 13:07:35 | 2011-04-19 13:07:35 |
| 32 | 9 | 13 | 2011-04-19 13:29:17 | 2011-04-19 13:29:17 |
| 33 | 9 | 14 | 2011-04-19 13:45:56 | 2011-04-19 13:45:56 |
| 34 | 9 | 15 | 2011-04-19 13:49:08 | 2011-04-19 13:49:08 |
| 35 | 14 | 16 | 2011-04-19 16:47:28 | 2011-04-19 16:47:28 |
| 36 | 4 | 16 | 2011-04-19 16:49:46 | 2011-04-19 16:49:46 |
| 37 | 13 | 16 | 2011-04-19 16:50:29 | 2011-04-19 16:50:29 |
| 38 | 15 | 5 | 2011-04-19 16:53:55 | 2011-04-19 16:53:55 |
| 39 | 15 | 16 | 2011-04-19 16:54:15 | 2011-04-19 16:54:15 |
| 40 | 15 | 8 | 2011-04-19 16:54:29 | 2011-04-19 16:54:29 |
| 41 | 15 | 4 | 2011-04-19 16:58:20 | 2011-04-19 16:58:20 |
| 42 | 15 | 0 | 2011-04-19 16:59:21 | 2011-04-19 16:59:21 |
| 44 | 9 | 4 | 2011-04-19 17:03:54 | 2011-04-19 17:03:54 |
| 45 | 9 | 16 | 2011-04-19 17:03:57 | 2011-04-19 17:03:57 |
| 46 | 13 | 8 | 2011-04-19 17:08:56 | 2011-04-19 17:08:56 |
| 47 | 13 | 6 | 2011-04-19 17:08:58 | 2011-04-19 17:08:58 |
| 48 | 13 | 17 | 2011-04-19 17:10:26 | 2011-04-19 17:10:26 |
| 49 | 18 | 16 | 2011-04-19 17:15:49 | 2011-04-19 17:15:49 |
| 50 | 18 | 4 | 2011-04-19 17:15:52 | 2011-04-19 17:15:52 |
| 51 | 18 | 1 | 2011-04-19 17:15:53 | 2011-04-19 17:15:53 |
| 52 | 18 | 5 | 2011-04-19 17:15:54 | 2011-04-19 17:15:54 |
| 53 | 18 | 17 | 2011-04-19 17:15:57 | 2011-04-19 17:15:57 |
| 54 | 19 | 17 | 2011-04-19 17:21:45 | 2011-04-19 17:21:45 |
| 55 | 19 | 18 | 2011-04-19 17:22:28 | 2011-04-19 17:22:28 |
| 56 | 22 | 19 | 2011-04-19 17:35:40 | 2011-04-19 17:35:40 |
| 57 | 26 | 20 | 2011-04-19 17:46:00 | 2011-04-19 17:46:00 |
| 58 | 26 | 21 | 2011-04-19 17:51:51 | 2011-04-19 17:51:51 |
| 59 | 26 | 22 | 2011-04-19 17:54:32 | 2011-04-19 17:54:32 |
| 60 | 26 | 23 | 2011-04-19 18:11:24 | 2011-04-19 18:11:24 |
| 61 | 26 | 24 | 2011-04-19 18:12:23 | 2011-04-19 18:12:23 |
| 62 | 27 | 2 | 2011-04-19 18:24:38 | 2011-04-19 18:24:38 |
| 63 | 27 | 25 | 2011-04-19 18:29:07 | 2011-04-19 18:29:07 |
| 64 | 27 | 26 | 2011-04-19 18:31:51 | 2011-04-19 18:31:51 |
| 65 | 27 | 3 | 2011-04-19 18:32:46 | 2011-04-19 18:32:46 |
+----+---------+---------+---------------------+---------------------+
Have you tried:
select count(id) as total,item_id from suggestions
group by item_id order by total desc
limit 9 offset 9;