1

I'm trying to select some records from a data extension where an email address appears more than once with a different date, using this query:

SELECT emailAddress, MIN(scheduleDate) AS scheduleDate
FROM CC_B2B_All_Info
GROUP BY emailAddress

This gives me the correct amount - 127 records, out of 403. Howevr, there are several other columns - position, slot and client, which I need to pull through as well. When I add these to the SELECT and GROUP BY clauses, the query then pulls through all 403 records instead of the 127. What am I doing wrong?

SELECT emailAddress, MIN(scheduleDate) AS scheduleDate, position, slot, client
FROM CC_B2B_All_Info
GROUP BY emailAddress, position, slot, client
0

Basically, your second SQL is grouping on four columns where 403 different combinations were given to you. The first SQL gave you the correct results you only had one combination of grouping which was on emailaddress.

Below I am using the ROW_NUMBER() function to assign a number to each row and group them by emailaddress and order by date in ascending.

I have then used that table as a subquery to output the first row by using the rankrow = 1 from the subquery.

Reference: ROW_NUMBER

Please see the revised SQL:

SELECT x.emailAddress, 
       x.scheduleDate, 
       x.position, 
       x.slot, 
       x.client
FROM (
    SELECT emailAddress, 
           ROW_NUMBER() OVER (PARTITION BY emailAddress ORDER BY scheduleDate ASC) AS RankRow, 
           scheduleDate, 
           position, 
           slot, 
           client
    FROM CC_B2B_All_Info
) x
WHERE x.RankRow = 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.