Select Minimum Date with SQL

Question

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

Answer 1

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