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.
SELECT logID, DateVisited, DealerVisited FROM dbo.fieldlog WHERE DateVisited IN (SELECT MAX(DateVisited) FROM dbo.fieldlog GROUP BY DateVisited) ORDER BY DealerVisited ASC
Both of these still return all 3 rows...
SELECT logID, DateVisited, DealerVisited FROM dbo.fieldlog A WHERE DateVisited = (SELECT MAX(DateVisited) FROM dbo.fieldlog WHERE DateVisited = A.DateVisited) ORDER BY DealerVisited ASC
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?