asked on

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

ASKER CERTIFIED SOLUTION

gothamite

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyberkiwi

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

purdyra1

ASKER

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!!

help using top &amp; distinct with mulitple columns

help using top & distinct with mulitple columns