question

subramanyam avatar image
subramanyam asked

Select query

I am trying to write a select query to get the latest activitydate for the accounts. select b.account, ap.InstallOSName, ay.ActDate, b.offerid, c.parentcommunityid, c.communityname, ct.hierarchy, ct.level, b.lastname + ', ' + b.firstname, b.startdate, b.agentconfigurationid, p.value from customer(nolock) b join profile p on b.account=p.account and p.attribute = 'ClientVersion' join community c on b.offerid = c.communityid join communitytree ct on c.communityid= ct.communityid join accountprofile ap on b.account=ap.account join activity ay on b.account=ay.account where b.status <> 'C' Account InstallOSName ActDate 10 Mac OS 2011-03-25 19:41:00.887 10 Mac OS 2011-03-25 19:41:06.413 11 Win Xp 2011-03-26 00:51:43.800 11 Win Xp 2011-03-26 00:52:08.387
tsqlselect
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
SELECT account, MAX(ActDate)... ...GROUP BY Account (and any other fields selected) Might be a starting point.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

(You may find that using a ROW_NUMBER style windowing function as @Kevin Feasel suggests works better, depending on how you group / select your data and how much of it you need to return...)
0 Likes 0 ·
Kevin Feasel avatar image
Kevin Feasel answered
Aside from the aggregation that @ThomasRushton suggests, you could also use a windowing function in a subquery, like so: select b.account, ap.InstallOSName, ay.ActDate, b.offerid, c.parentcommunityid, c.communityname, ct.hierarchy, ct.level, b.lastname + ', ' + b.firstname, b.startdate, b.agentconfigurationid, p.value from customer(nolock) b join profile p on b.account=p.account and p.attribute = 'ClientVersion' join community c on b.offerid = c.communityid join communitytree ct on c.communityid= ct.communityid join accountprofile ap on b.account=ap.account join ( select account, ActDate, ROW_NUMBER() over (partition by account order by ActDate desc) as rownum from activity ) ay on b.account=ay.account and ay.rownum = 1 where b.status 'C' The ROW_NUMBER() will, for each account, return the rows descending by activity date. Then, your join filters out all but the ones where rownum is 1, as you just want the latest. If this is a very common query, it may also be a good idea to add an index to the activity table, including the account and activity date as columns. That way, you can get a covering index, which would improve performance for this query (though you'll have to see if the drag on insert and update operations is worth the cost of the index). I would recommend trying @ThomasRushton's solution as well as this one and see which performs better; different workloads may lead to different results.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.