Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

ORDER BY items must appear in the select list if SELECT DISTINCT is specified

Avatar of knobbylowboy
knobbylowboy asked on
Microsoft SQL Server 2005SQL
27 Comments1 Solution528 ViewsLast Modified:
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
FROM table1
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)
ASKER CERTIFIED SOLUTION
Avatar of Rajkumar Gs
Rajkumar GsFlag of India imageSoftware Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 27 Comments.
See Answers