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)
Users
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

Items
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

purdyra1Asked:
Who is Participating?
 
gothamiteCommented:
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
ORDER BY MAX(Timestamp) DESC

Open in new window

0
 
cyberkiwiCommented:
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
0
 
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.  

thanks!!
0
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.