SQL Question

[lozina]

Suppose you have the following situation,

a table named 'activity' with three columns as follows:

PAGE_ID - NUMBER
USER_ID - NUMBER
LAST_ACTIVE_DT - DATE/TIME

a web site which has server side code which inserts/updates a row in above table each time they visit or revisit a particular page.

now, you want a report where you get the the last active page the user was on.

how do you do this in one select query?

I cannot do:
select user_id, max(last_active_dt) from activity group by user_id

because while the resulting date would be correct I have no idea what page it was associated with

then I cannot add page_id into the group by expression because then it would separate the results by page but I only want the one last page a user visited and the date of that visit.

Example: data as follows: (bah, attach code is teh suck)

PAGE_ID | USER_ID | LAST_ACTIVE_DT |
--------+---------+----------------|
1 | 1 | 7/10/2008 |
2 | 1 | 8/15/2008 |
3 | 1 | 7/25/2008 |
2 | 2 | 9/15/2008 |
4 | 2 | 8/10/2008 |
--------+---------+----------------+

And the result of query would be:

2, 1, 8/15/2008
2, 2, 9/15/2008

 

[Hmongkeysauce]

Probably something like:
SELECT DISTINCT(user_id), page_id, MAX(last_active_dt) FROM activity GROUP BY user_id, page_id

 

[Crusty]

SELECT user_id, page_id, MAX(last_active_dt) as access_time FROM activity GROUP BY user_id, page_id ORDER BY access_time DESC LIMIT 1;

 

[MrChad]

SELECT user_id, page_id, last_active_dt FROM activity WHERE last_active_dt = (SELECT MAX(last_active_dt) FROM activity a WHERE a.user_id = user_id)

 

[lozina]

Originally posted by: MrChad
SELECT user_id, page_id, last_active_dt FROM activity WHERE last_active_dt = (SELECT MAX(last_active_dt) FROM activity a WHERE a.user_id = user_id)

Although this query didn't exactly achieve the results desired you get the prize because it led me to a query that seems to work. the original query was only returning one row for the entire table, just the very last person who had any activity, but I needed a record for every unique user. So I went with this:

select a. page_id, a.user_id, a.last_active_dt
from (select user_id, max(last_active_dt) as last_active_dt
from activity
group by user_id) x, activity a
where a.user_id = x.user_id
and a.last_active_dt = x.last_active_dt
order by a.edit_user_nm

The key being from your query you use the date as sort of a key which works here because the date is a timestamp with very fine precision so the chances of two people recording rows for the exact same timestamp are virtually impossible. so that's why it works, thanks!

 

[Cogman]

Why not do a Select * from blah. Since you want all the data anyways.

 

[lozina]

Originally posted by: Cogman
Why not do a Select * from blah. Since you want all the data anyways.

I needed one row for each user. The data has multiple rows per user but I just wanted the one row that has their most recent activity, for each unique user in the system.

 

[Evadman]

Select
activity.PAGE_ID, activity.USER_ID, activity.LAST_ACTIVE_DT
from
(Select USER_ID, max(LAST_ACTIVE_DT) as LAST_ACTIVE_DT from activity group by USER_ID) lastactive inner join activity
ON lastactive.USER_ID = activity.USER_ID AND lastactive.LAST_ACTIVE_DT = activity.LAST_ACTIVE_DT