SQL Query over ODBC is slow with DISTINCT Is there any way to simplify this query? It's being run against a Progress Database via an ODBC connection.

The CustomerSalesperson table has a CustomerID, a SalespersonID, and a CreateDate. Each CustomerID could have many records, each with a different SalespersonID. I'd like to get a list of the SalespersonIDs with the most recent CreateDate for each CustomerID. This is what I'm doing currently:

SELECT DISTINCT cs.CustomerID, cs.SalespersonID
FROM PUB.CustomerSalesperson cs
INNER JOIN
  (SELECT CustomerID, MAX(CreateDate) AS MaxDate
  FROM PUB.CustomerSalesperson
  GROUP BY CustomerID) csmax
ON cs.CustomerID = csmax.CustomerID
WHERE csmax.MaxDate = cs.CreateDate


It take a little longer to return than I would expect. When I remove the DISTINCT, it goes a lot faster, but it returns many duplicates as a result of other fields in the table not which aren't relevant to the query.
This is a messy query, I personally don't like nesting a select statement in my joins, I would rather use a Common Table Expression like this

WITH csmax AS (
    SELECT CustomerID, MAX(CreateDate) AS MaxDate
        FROM PUB.CustomerSalesperson
        GROUP BY CustomerID
)
SELECT DISTINCT cs.CustomerID, cs.SalespersonID
    FROM PUB.CustomerSalesperson cs
    INNER JOIN csmax ON cs.CustomerID = csmax.CustomerID
    WHERE csmax.MaxDate = cs.CreateDate


This should help the DB run the query more efficiently as well.  using the with statement should allow the SQL Engine to create a table and then compare the tables the way they were meant to be compared.  The CTE actually prepares the table before the outer query is run, so we aren't doing this on the fly.



Sounds like you can't run this through the ODBC connection, I looked at the documentation that you linked me to and found a CREATE VIEW command that would probably have similar efficiency to using a CTE.

CREATE VIEW csmax (CustomerID, CreateDate) AS 
    SELECT CustomerID, MAX(CreateDate) AS MaxDate
        FROM PUB.CustomerSalesperson
        GROUP BY CustomerID ;

SELECT DISTINCT cs.CustomerID, cs.SalespersonID
    FROM PUB.CustomerSalesperson cs
    INNER JOIN csmax ON cs.CustomerID = csmax.CustomerID
    WHERE csmax.MaxDate = cs.CreateDate

DROP VIEW csmax


This code is more readable than the code that you wrote, it is easier to follow.  

First thing you want to do is get a list of all the customers and the most recent date they did whatever.

Then, you want to know which Sales Person is associated with the "whatever" that customer did at their most recent visit.