SQL Selection 1

[fiber0pti]

I have a table, contactInfo. In it there are two columns, contactDate and customerID. I'm trying to create query that returns the single most recent row for each customerID within the table. Of course, there are mutliple rows for each customer. Any ideas?

 

[PHV]

SELECT customerID, Max(contactDate) As LastContact
FROM contactInfo
GROUP BY customerID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fiber0pti]

I just realized I needed more information returned. I'm using the query you gave me above. However, I'm also needing to return the number of orders associated with the customerID. These orders can be found in the table orderID with an associated customerID column. Is there a way to return a column with the number of orders for each of the customers recent contacts or is it better to just do seperate queries?

 

[PHV]

SELECT C.customerID, Max(contactDate) As LastContact, Count(O.customerID) As NumberOfOrders
FROM contactInfo C LEFT JOIN orderID O ON C.customerID = O.customerID
GROUP BY C.customerID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fiber0pti]

SELECT C.customerID, Max(contactDate) As LastContact, Count(O.customerID) As NumberOfOrders
FROM contactInfo C LEFT JOIN orderID O ON C.customerID = O.customerID
GROUP BY C.customerID

returns an error: Invalid object orderID. Even if I change the query to read.

SELECT customers.customerID, Max(contactDate) As LastContact, Count(orders.customerID) As NumberOfOrders
FROM contactInfo customers LEFT JOIN orderID orders ON customers.customerID = orders.customerID
GROUP BY customers.customerID

 

[PHV]

Replace orderID by the REAL name of the orders table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fiber0pti]

I'm using the following query

SELECT DISTINCT 
                      contactInfo.customerID, MAX(DISTINCT contactInfo.contactDate) AS LastContact, customers.firstName, customers.lastName, customers.companyName, 
                      salesPeople.salesPerson
FROM         contactInfo INNER JOIN
                      customers ON contactInfo.customerID = customers.customerID INNER JOIN
                      salesPeople ON contactInfo.createdBy = salesPeople.salesPersonID
GROUP BY contactInfo.customerID, customers.firstName, customers.lastName, customers.companyName, salesPeople.salesPerson
ORDER BY contactInfo.customerID

This works great with one exception, it returns multiple rows of a customer contact. Example: If two sales people contact a customer this query will return two rows for the customer, one for each sales rep. Any ideas?

 

[PHV]

Something like this ?
SELECT I.customerID, I.contactDate AS LastContact, C.firstName, C.lastName, C.companyName, S.salesPerson
FROM contactInfo I
INNER JOIN customers C ON I.customerID = C.customerID
INNER JOIN salesPeople S ON I.createdBy = S.salesPersonID
INNER JOIN (
SELECT customerID, MAX(contactDate) AS LastContact GROUP BY customerID
) M ON I.customerID = M.customerID AND I.contactDate = M.LastContact
ORDER BY I.customerID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fiber0pti]

When I use that query I get several errors:
Invalid column name 'customerID'
Invalid column name 'contactDate'
Invalid column name 'customerID'

 

[PHV]

Sorry for the typo.
SELECT I.customerID, I.contactDate AS LastContact, C.firstName, C.lastName, C.companyName, S.salesPerson
FROM contactInfo I
INNER JOIN customers C ON I.customerID = C.customerID
INNER JOIN salesPeople S ON I.createdBy = S.salesPersonID
INNER JOIN (
SELECT customerID, MAX(contactDate) AS LastContact FROM contactInfo GROUP BY customerID
) M ON I.customerID = M.customerID AND I.contactDate = M.LastContact
ORDER BY I.customerID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886