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)
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 ;
I'm glad I could help. Please mark the answer as correct so others know. Thanks.
16 People are following this question.
