CODEWITHSUNDEEP
mysqlsql
Flukey
Flukey

Reputation: 6555

Return most recent visits to a users profile. Group-by-N confusion

I have a user_profile_view table:

The definition is as follows:

+------------+------------+------+-----+---------+----------------+
| Field      | Type       | Null | Key | Default | Extra          |
+------------+------------+------+-----+---------+----------------+
| id         | bigint(20) | NO   | PRI | NULL    | auto_increment |
| user_id    | bigint(20) | NO   | MUL | NULL    |                |
| viewer_id  | bigint(20) | NO   | MUL | NULL    |                |
| created_at | datetime   | NO   |     | NULL    |                |
| updated_at | datetime   | NO   |     | NULL    |                |
+------------+------------+------+-----+---------+----------------+

I'm trying to get the most recent visits to a users profile but I only want to display the visitor once

A simple query such as this will return the most recent visits on a profile:

 SELECT v.* FROM user_profile_view v 
 WHERE v.user_id = 2 AND v.viewer_id != 2
 ORDER BY created_at DESC LIMIT 0,20;

The result set:

+------+---------+-----------+---------------------+---------------------+
| id   | user_id | viewer_id | created_at          | updated_at          |
+------+---------+-----------+---------------------+---------------------+
| 1314 |       2 |         1 | 2011-11-20 18:50:35 | 2011-11-20 18:50:35 |
| 1311 |       2 |         1 | 2011-11-17 00:51:10 | 2011-11-17 00:51:10 |
| 1307 |       2 |         1 | 2011-11-16 19:28:51 | 2011-11-16 19:28:51 |
| 1301 |       2 |         1 | 2011-11-16 19:08:08 | 2011-11-16 19:08:08 |
| 1299 |       2 |         1 | 2011-11-16 17:46:47 | 2011-11-16 17:46:47 |
| 1295 |       2 |         1 | 2011-11-16 17:34:09 | 2011-11-16 17:34:09 |
| 1277 |       2 |         1 | 2011-11-16 16:54:11 | 2011-11-16 16:54:11 |
| 1270 |       2 |         1 | 2011-11-16 15:51:09 | 2011-11-16 15:51:09 |
| 1109 |       2 |         1 | 2011-11-01 20:56:39 | 2011-11-01 20:56:39 |
| 1108 |       2 |         1 | 2011-11-01 20:56:34 | 2011-11-01 20:56:34 |
| 1100 |       2 |         1 | 2011-11-01 18:48:06 | 2011-11-01 18:48:06 |
| 1098 |       2 |         1 | 2011-11-01 18:43:44 | 2011-11-01 18:43:44 |
| 1097 |       2 |         1 | 2011-11-01 18:43:34 | 2011-11-01 18:43:34 |
|  950 |       2 |         1 | 2011-10-05 20:07:37 | 2011-10-05 20:07:37 |
|  948 |       2 |         1 | 2011-10-05 19:39:03 | 2011-10-05 19:39:03 |
|  944 |       2 |         1 | 2011-10-05 19:12:02 | 2011-10-05 19:12:02 |
|  941 |       2 |         1 | 2011-10-05 19:06:35 | 2011-10-05 19:06:35 |
|  935 |       2 |         1 | 2011-10-05 18:40:17 | 2011-10-05 18:40:17 |
|  933 |       2 |         1 | 2011-10-05 18:18:31 | 2011-10-05 18:18:31 |
|  932 |       2 |         1 | 2011-10-05 18:14:08 | 2011-10-05 18:14:08 |
+------+---------+-----------+---------------------+---------------------+
20 rows in set (0.00 sec)

However I only want to return the visitor once ordered by their last visit date. So if a user visits a users profile three times in a row, i'll have 3 records in the user_profile_view_table but I only want to return the record of their last visit.

I need to use Group-by-N for this but i'm stuck.

This is what I have currently thought of:

SELECT v.viewer_id FROM user_profile_view v 
WHERE v.viewer_id != 2 AND v.user_id = 2
AND NOT EXISTS (SELECT * FROM user_profile_view v2
                WHERE v2.viewer_id = v.viewer_id AND v2.created_at < v.created_at)
ORDER BY created_at DESC;

Upvotes: 1

Views: 64

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

SELECT v.viewer_id, MAX(created_at) AS MaxCreated
    FROM user_profile
    WHERE viewer_id != 2
        AND v.user_id = 2
    ORDER BY MaxCreated
    GROUP BY v.viewer_id;

Upvotes: 2

Thom Wiggers
Thom Wiggers

Reputation: 7052

This will work in strict SQL and only return 3 rows:

SELECT v.viewer_id
FROM user_profile_view v
WHERE v.viewer_id != 2 AND v.user_id = 2
    AND v.created_at = (SELECT MAX(created_at)
                       FROM user_profile
                       WHERE user_id = v.user_id)
ORDER BY v.created_at DESC
LIMIT 3;

Upvotes: 1

Related Questions