Returning Most Recent Record Per Group

[n1concepts]

See table info - listing all records for 'users_id' column:

------------------

mysql> SELECT id,users_id, subscriptions_id,created from user_subscriptions;
+-----+----------+------------------+---------------------+
| id  | users_id | subscriptions_id | created             |
+-----+----------+------------------+---------------------+
| 108 |      170 |                5 | 2013-06-04 14:29:59 |
| 109 |      171 |                5 | 2013-06-04 17:23:16 |
| 110 |      177 |                5 | 2013-06-13 17:24:01 |
| 111 |      177 |                5 | 2013-06-13 19:37:41 |
| 112 |      178 |                5 | 2013-06-13 19:46:04 |
| 113 |      178 |                1 | 2013-06-13 19:47:02 |
| 114 |      178 |                5 | 2013-06-13 20:32:01 |
| 115 |      178 |                5 | 2013-06-13 20:50:36 |
| 116 |      180 |                5 | 2013-06-14 22:45:00 |
| 117 |      181 |                5 | 2013-06-17 16:42:24 |
| 118 |      183 |                5 | 2013-06-17 16:51:25 |
| 120 |      184 |                5 | 2013-06-21 16:52:36 |
| 121 |      184 |                5 | 2013-06-21 16:57:39 |
| 122 |      169 |                5 | 2013-06-21 21:12:27 |
| 123 |      169 |                5 | 2013-06-24 14:02:52 |
| 124 |      169 |                5 | 2013-06-26 15:32:23 |
| 125 |      185 |                5 | 2013-06-27 14:28:10 |
| 126 |      186 |                5 | 2013-06-29 05:34:39 |
| 127 |      187 |                5 | 2013-07-01 22:15:42 |
| 128 |      169 |                5 | 2013-07-02 19:28:58 |
| 129 |      169 |                5 | 2013-07-08 13:27:39 |
| 130 |      169 |                5 | 2013-07-08 13:49:35 |
| 131 |      169 |                5 | 2013-07-08 13:51:08 |
| 132 |      169 |                5 | 2013-07-08 14:37:25 |
| 133 |      169 |                5 | 2013-07-08 20:02:56 |
| 134 |      169 |                1 | 2013-07-08 20:13:05 |
| 135 |      169 |                2 | 2013-07-08 20:25:38 |
| 136 |      190 |                5 | 2013-07-12 13:48:49 |
| 137 |      191 |                5 | 2013-07-12 14:49:06 |
+-----+----------+------------------+---------------------+
29 rows in set

Note: I'm trying to figure out the correct query that will list (only) the most recent entries - highlight in Bold above - which is a total of 14 entries (you can see there are 14 individaul 'users_id' values (shown below so to confirm the count that should be returned from the above query.)

mysql> select DISTINCT(users_id) from user_subscriptions;
+----------+
| users_id |
+----------+
|      170 |
|      171 |
|      177 |
|      178 |
|      180 |
|      181 |
|      183 |
|      184 |
|      169 |
|      185 |
|      186 |
|      187 |
|      190 |
|      191 |
+----------+
14 rows in set

mysql>

Objective:

 

Define a MySQL select statement that will filter and only display the most recent record entry - for each 'users_id' column which will include subscriptions_id.

I have been looking at using MAX() to filter and GROUPING on users_id but having issues w/that - reason I'm asking for help (LOL!)

 

FYI: The below statement gets me the date info i need but that's where I'm concerned - what if there are multiple entries (although there should be if constraint on that column - i have to check). but what is there are two dates that are the same? This is my question and reason I'm thinking I need to filter/group on both - 'created' column and 'users_id'?

mysql> SELECT MAX(created) max_date FROM user_subscriptions GROUP BY users_id;
+---------------------+
| max_date            |
+---------------------+
| 2013-07-08 20:25:38 |
| 2013-06-04 14:29:59 |
| 2013-06-04 17:23:16 |
| 2013-06-13 19:37:41 |
| 2013-06-13 20:50:36 |
| 2013-06-14 22:45:00 |
| 2013-06-17 16:42:24 |
| 2013-06-17 16:51:25 |
| 2013-06-21 16:57:39 |
| 2013-06-27 14:28:10 |
| 2013-06-29 05:34:39 |
| 2013-07-01 22:15:42 |
| 2013-07-12 13:48:49 |
| 2013-07-12 14:49:06 |
+---------------------+
14 rows in set

I would appreciate some input to help me finish building this query to produce the unique rows based on most recent entry (per 'users_id').

 

Thx!

 

[mac_gyver]

http://dev.mysql.com/doc/refman/5.6/en/example-maximum-column-group-row.html

[n1concepts]

Never mind - I got it (Dah!) - thx

mysql> SELECT users_id,id,subscriptions_id FROM user_subscriptions 
where created IN (SELECT MAX(created) max_date FROM user_subscriptions GROUP BY users_id) ORDER BY users_id DESC;
+----------+-----+------------------+
| users_id | id  | subscriptions_id |
+----------+-----+------------------+
|      191 | 137 |                5 |
|      190 | 136 |                5 |
|      187 | 127 |                5 |
|      186 | 126 |                5 |
|      185 | 125 |                5 |
|      184 | 121 |                5 |
|      183 | 118 |                5 |
|      181 | 117 |                5 |
|      180 | 116 |                5 |
|      178 | 115 |                5 |
|      177 | 111 |                5 |
|      171 | 109 |                5 |
|      170 | 108 |                5 |
|      169 | 135 |                2 |
+----------+-----+------------------+
14 rows in set