question

goza1515 avatar image
goza1515 asked

How to find the top 2 records for each unique id

I am looking to get the top two activity dates for each unique id in my query. when i select distinct it only brings the top two assuming i don't have a proper group by to get it done can anyone help?? Select Contacts.ContactID AS "Partner ID", Activity.ActivityDate AS "Activity Date" FROM Contacts WITH(NOLOCK) LEFT OUTER JOIN Activity WITH(NOLOCK) ON Contacts.UniqueID = Activity.ContactID LEFT OUTER JOIN dbo.[v_LastGift] (nolock) ON Contacts.UniqueID = v_LastGift.ContactID WHERE (Contacts.UniqueID IN (SELECT Contacts.UniqueID AS "Contacts.UniqueID" FROM Contacts WITH(NOLOCK) WHERE ((SELECT TOP 1 Activity.Value FROM Activity WITH(NOLOCK) WHERE Activity.ContactID = Contacts.UniqueID and Activity.Value > '0' ORDER BY Activity.Activitydate ASC) >= 1.00 AND Contacts.UniqueID IN (SELECT Invoices.ContactID AS "InvoiceItems.ContactID" FROM InvoiceItems WITH(NOLOCK) JOIN Invoices WITH(NOLOCK) ON InvoiceItems.InvoiceID = Invoices.UniqueID WHERE (Invoices.ItemNet + Invoices.ShippingCost + Invoices.HandlingCost + Invoices.GiftNet + Invoices.TaxAmount >= 1.00) GROUP BY Invoices.ContactID HAVING SUM(InvoiceItems.Quantity) >= 1))) AND isnull(dbo.[v_LastGift].GiftCount,0) >= 2)
selectdistinctunique
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.

1 Answer

· Write an Answer
Tom Staab avatar image
Tom Staab answered
I was trying to figure out a method using the newer SQL Server 2012 window functions instead of a CTE, but I haven't yet. This should work, though. It will return 2 rows per contact for the most recent and 2nd most recent activity dates. If you want one row per contact, just pivot the results. WITH OrderedData AS ( SELECT c.ContactId, a.ActivityDate , ListOrder = ROW_NUMBER() OVER (PARTITION BY c.ContactId ORDER BY a.ActivityDate DESC) FROM dbo.Contacts c LEFT JOIN dbo.Activity a ON a.ContactId = c.UniqueId ) SELECT od.ContactId, od.ActivityDate FROM OrderedData od WHERE od.ListOrder < 3 ORDER BY ContactId, ActivityDate DESC ;
2 comments
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.

Thank you Tom That Did work!
0 Likes 0 ·
I'm glad I could help. Please mark the answer as correct so others know. Thanks.
0 Likes 0 ·

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.