I have a table where I store a history of what the user has looked at and I want to retrieve the 5 most recent things the user has looked at, without any duplicates. I tried the query below (table/column names changed for clarity) but get an error: "ORDER BY items must appear in the select list if SELECT DISTINCT is specified". If I add the datstamp column to the select list then that row is no longer distinct and I get duplicates of course. How can I rewrite this query so that it does what I want, and as this query will be run very frequently I am looking for a solution that is efficient.
SELECT DISTINCT TOP 5
table1.id, table2.name, table3.name
LEFT OUTER JOIN table2 ON table2.id = table1.table2id
LEFT OUTER JOIN table3 ON table3.id = table1.table3id
WHERE table1.userid = @userid
ORDER BY table1.datestamp
(Running on SQL Server 2005 Express Edition)