Select rows who's FIRST result is a specific value
I've got a custom written analytics system running and I'm trying to write
a query that returns users who landed on a specific page as their first
hit. The relevant parts of the table is setup as such, with some simple
data:
pageviews Table
+----+---------------------+----------+-------------+
| id | time_in | users_id | articles_id |
+----+---------------------+----------+-------------+
| 0 | 2013-08-15 00:00:00 | 0 | 0 |
| 1 | 2013-08-16 00:00:00 | 0 | 1 |
| 2 | 2013-08-17 00:00:00 | 1 | 1 |
| 3 | 2013-08-18 00:00:00 | 1 | 0 |
| 4 | 2013-08-19 00:00:00 | 1 | 1 |
| 5 | 2013-08-20 00:00:00 | 2 | 1 |
+----+---------------------+----------+-------------+
NOTE: The ID fields in my DB are actually using GUIDs, not ints as in this
simple example.
Now, if I want to see who read article 1 as their first hit, I want my
query to return users 1 and 2, but not 0, as user 0 saw article 0 as their
first hit on the site. Conversely, if I want to see who read article 0
first, the query would only return user 0.
Here is my query thus far:
SELECT
*
FROM
pageviews
WHERE
articles_id = 1
GROUP BY
users_id
ORDER BY
time_in
But this returns distinct user IDs for all users who've read article 1,
not filtering out the users who did not see it as their first result. I
feel like I'm going the wrong direction with my query, so I'm turning
towards you guys.
Thanks in advance.
No comments:
Post a Comment