Distinct in join - please help

[xaphod]

Hi, Whenever a user modifies a customers data I create a log with only the id’s(int) and the datemodified(datetime). Then I want to view the 10 latest customers that the user modified, sorted by datemodyfied and with unique customernames. So I want to select Customers.customerid and Customers.customername sorted by entries in table CustomerLogs, sorted by CustomerLogs.datemodified. No doubles of customername allowed. I’ve tried some different joins but nothing works exactly as I want. In a WHERE clause I have author_userid=2 AND owner_companyid=1 Can somebody give some advice, please? I use SQL Server 2000 TABLE Customers Customerid | customername TABLE CustomerLogs customerlogid | customerid | author_userid | datemodified | owner_companyid

[Michael Potter]

I think it may look something like this:

SELECT TOP 10
    Customers.customerid,
    Customers.customername,
    Log.LastModified
FROM
    Customers
INNER JOIN
    (SELECT customerId, Max(dateModified) AS LastModified
     FROM CustomerLogs
     GROUP BY customerId) as Log
    ON (Customers.customerId = Log.customerId)
ORDER BY LastModified DESC

If your log is large - you will want an index on (customerId, dateModified)

[xaphod]

Thanks Michael! You really made my day! Your solution works excellent!:-D Have a nice day!