Use Partition in SQL

Question

I have a problem with a query. Here is the query.

SELECT UserID, MAX(UserName) as UserName, MAX(TransactionTime) as TransactionTime,         MAX(LastAction) as LastAction 
FROM UserActivities 
WHERE OrganizationID = 26465
GROUP BY UserID

There are so many records for particular user at different TransactionTime. I want to take LastAction along with other records. How can I do it? Is SQL partition will work here?

Andreas Ågren · Accepted Answer · 2011-08-11 08:18:07Z

3

A ranking function is probably what you are looking for:

SELECT *
FROM (
   SELECT UserID, UserName, LastAction, row_number() over(partition by UserId order by TransactionTime desc) RowNo
   FROM UserActivities 
   WHERE OrganizationID = 26465
) t
where t.RowNo = 1

answered Aug 11, 2011 at 8:18

Andreas Ågren

3,94926 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use Partition in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related