Cant figure out the query

Here's the deal. I've got 2 tables:

user
==========
int uid
varchar firstname
varchar lastname

log
==========
int uid
datetime date
varchar page

Every time someone accesses a page on my system, if they're logged in, I create an entry in log with their uid and the url of the page they're accessing.

Now, I'd like to see a list of the last 5 peoples names to access the site. I can't figure out how to write a query to get that.

Sample Data:

user
===============
233 Joe Blow
1150 Tom Sawer
429 Mike Christy

log
===============
233 3/1/2006 7:54:39 AM /trips/payments.asp
233 3/1/2006 7:54:44 AM /trips/whosgoing.asp
233 3/1/2006 7:54:48 AM /login.asp
1150 3/1/2006 8:34:25 AM /trips/payments.asp
1150 3/1/2006 8:34:28 AM /trips/whosgoing.asp
1150 3/1/2006 8:34:36 AM /trips/payments.asp
429 3/1/2006 9:27:22 AM /profile.asp
429 3/1/2006 9:27:35 AM /trips/Default.asp
429 3/1/2006 9:27:38 AM /default.asp


So, what I'd like is:

result
========================
3/1/2006 9:27:38 AM Mike Christy /default.asp
3/1/2006 8:34:36 AM Tom Sawyer /trips/payment.asp
3/1/2006 7:54:48 AM Joe Blow /login.asp


Hope that makes sense.

Thanks in advance for the help.

- O

I assume you're using MSSQL...

Join the tables and use the DISTINCT keyword:

SELECT TOP 5 DISTINCT
U.uid, U.firstname, U.lastname,
L.date, L.page
FROM [log] L LEFT JOIN [user] U ON L.uid = U.uid
ORDER BY L.date DESC

thanks for the reply, but its not that simple (if it was, i wouldn't have posted

the problem with the query that you provided is that, since i'm using a datetime field, every single entry is unique. so if i have 1 user browse the system and go through 5 pages, i'll just see that user name 5 times (since each datetime is unique).

i actually got this problem solved over at the devshed forums. here was the final solution:

SELECT TOP 5
L1.yourdate,
CONCAT(firstname lastname) AS name
L1.page
FROM
user
INNER JOIN log AS L1
ON user.uid=L1.uid
INNER JOIN log AS L2
ON L1.uid=L2.uid
GROUP BY
L1.yourdate,
name,
L1.page
HAVING L1.yourdate=MAX(L2.yourdate)
ORDER BY L1.yourdate DESC

yep, that works!