SQL Statement with DISTINCT data

[dudeshouse]

Hi All,

I've got a SQL Server database with the following structure in a table called fieldlog:

logID | DateVisited | DealerVisited
1 | 20/10/2010 | 123456
2 | 21/10/2010 | 123456
3 | 21/10/2010 | 456789

I'm trying to create a recordset that return the last visit to a particular dealer. I've tried using DISTINCT, MAX and a nested SELECT but so far haven't managed to get the right set of data out. It always only returns either one row or all 3.

egs:
SELECT logID, DateVisited, DealerVisited FROM dbo.fieldlog WHERE DateVisited IN (SELECT MAX(DateVisited) FROM dbo.fieldlog GROUP BY DateVisited) ORDER BY DealerVisited ASC
SELECT logID, DateVisited, DealerVisited FROM dbo.fieldlog A WHERE DateVisited = (SELECT MAX(DateVisited) FROM dbo.fieldlog WHERE DateVisited = A.DateVisited) ORDER BY DealerVisited ASC
Both of these still return all 3 rows...

I'm sure it's something simple I'm missing but I'm getting a little close to this to notice.

Can someone please help?

Thanks.

[shadowmaniac]

*Scrapped*. Flawed logic from my part.

[phpdeveloper]

Try below mysql query :

SELECT DISTINCT DealerVisited, DateVisited, logID FROM dbo.fieldlog group by DealerVisited order by DateVisited desc

[dudeshouse]

Thanks for the replies.

I've got the answer now, tested and working.

SELECT t.logid, t.DealerVisited, t.DateVisited FROM ( SELECT DealerVisited, MAX(DateVisited) AS latest_visit FROM dbo.fieldlog GROUP BY DealerVisited ) AS m INNER JOIN dbo.fieldlog AS t ON t.DealerVisited = m.DealerVisited AND t.DateVisited = m.latest_visit

The offering from phpdeveloper will still return all rows as they all fall under the DISTINCT category.

Thanks all for your time.