x

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)
more ▼

asked Aug 05, 2015 at 04:33 PM in Default

avatar image

goza1515
80 4

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
 ;

more ▼

answered Aug 05, 2015 at 05:30 PM

avatar image

Tom Staab ♦
14.5k 7 14 21

Thank you Tom That Did work!

Aug 05, 2015 at 06:04 PM goza1515

I'm glad I could help. Please mark the answer as correct so others know. Thanks.

Aug 05, 2015 at 06:45 PM Tom Staab ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x157
x22
x21

asked: Aug 05, 2015 at 04:33 PM

Seen: 91 times

Last Updated: Aug 05, 2015 at 06:45 PM

Copyright 2017 Redgate Software. Privacy Policy