Psycnosis Psycnosis - 1 year ago 73
SQL Question

How to select last entry per ID in SQL

I have a big log table with 2 million rows give or take.

I am looking to look for the last entry for each id.

The 3 columns of importance are

  • Userid

  • Actiontype

  • Actiontime

  • Text2

Some userids show up thousands of times some just show up once. I need the most recent of each userid. I tried to use 'Group By' but it wont work because text2 is different for each entry which is really the data I need. So it needs to be ordered by actiontime, actiontype needs to be 103. I am really at a loss how to do this.

Any help would be appreciated.

Answer Source
Select B.*
 From  (
        Select UserID,ActionTime=max(ActionTime) 
         From  SomeTable
         Group By UserID
       ) A
 Join SomeTable B on A.UserID=B.UserID and A.ActionTime=B.ActionTime
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download