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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.