help using top & distinct with mulitple columns

I have 2 tables, a users table and an items table that tracks events that take place(such as logged in, logged out, etc.)

I want to get info from both tables and I need to get the last 3 distinct users that have logged in recently.  I've tried some examples I've searched for but I must be doing something wrong or ??
thx for your help.

I need to get a return display of

userid      username     firstname     timestamp

tables are:  (this is a small sample, I have hundreds of rows)
userid, username, fullname
123-456, bobp,            bob price
123-457, johnd,            john doe
123-458, suem,            sue mazza
123-459, rodp,        rod parks
123-460, wandaj,      wanda jones

userid   timestamp  eventid
123-456, 4-11-2010  1
123-457, 4-19-2010  1
123-460, 4-12-2010  1
123-459, 4-02-2010  1
123-458, 4-15-2010  1

Who is Participating?
Try this:

SELECT TOP 3 u.userid,u.username,u.fullname,MAX(Timestamp) FROM users u INNER JOIN items i on u.userid=i.userid
GROUP BY u.userid,u.username,u.fullname

Open in new window

You can use the ranking function in sql 2005

select *
from (
      select userid, [timestamp],
      _rank=row_number() over (partition by userid order by i.[timestamp] desc)
      from items
) ranked inner join users on users.user_id = ranked.user_id
where ranked._rank <= 3
purdyra1Author Commented:
I didn't get a chance to try the other possible solution.

gothamite's solution seemed easier for my brain to follow and I tested it and it worked.  

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.