sql join - only select top row from 2nd table...

Question

Bit of an sql noob, have a list in table a of customercodes/phone numbers, and table b has all the call records.

I want to select the most recent call from table b for each of the customercodes/phone numbers in table a.

So far I have:

SELECT     A.CustomerCode, A.PhoneNumber, B.StartTime
FROM         tableA A INNER JOIN
             tableB B ON ( A.PhoneNumber = B.PhoneNumber 
                           AND A.CustomerCode = B.CustomerCode )
ORDER BY A.CustomerCode, A.CLI, B.StartTime DESC

But that is bringing up all the results from TableB. I literally just want to get the most recent call if that makes sense?

Answer 1

If you only want the starttime from table b, you could use a group by

SELECT     A.CustomerCode, A.PhoneNumber, max(B.StartTime)
FROM         tableA A INNER JOIN
             tableB B ON A.PhoneNumber = B.PhoneNumber 
                         AND A.CustomerCode = B.CustomerCode
GROUP BY A.CustomerCode, A.PhoneNumber
ORDER BY A.CustomerCode, A.CLI

Answer 2

Something like this?

SELECT A.CustomerCode, A.PhoneNumber, Max(B.StartTime) AS MaxOfStartTime
FROM A INNER JOIN B ON A.PhoneNumber = B.PhoneNumber
GROUP BY A.CustomerCode, A.PhoneNumber;

Answer 3

You can join the calls table on itself, and specify that no later row may exist. Like:

SELECT       A.CustomerCode, A.PhoneNumber, B.StartTime
FROM         tableA A 
INNER JOIN   tableB B 
ON           A.PhoneNumber = B.PhoneNumber 
AND          A.CustomerCode = B.CustomerCode
LEFT JOIN    tableB later
ON           B.PhoneNumber = later.PhoneNumber 
AND          B.CustomerCode = later.CustomerCode
AND          later.StartTime > B.StartTime
WHERE        later.PhoneNumber is null

The condition later.PhoneNumber is null says there can't be a later row. If there are multiple rows with the same StartTime, this will select all of them.

This allows you to select all columns from the latest call.