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

Question

This is my error

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

This is my subquery

(select TOP(1) Error from tablename v, tablename j where v.columname= j.columnname1 ORDER BY v.columnname,j.columnname1)

I'm confused because I am using an order by clause, any idea's?

Thank you in advance

Answer 1

Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax.

you can fix the problem using GROUP BY. This allows you to use aggregation functions on columns in the ORDER BY:

select TOP (1) Error
from tablename v join
     tablename j 
     on v.columname = j.columnname1
group by Error
order by max(v.columnname), max(j.columnname1);

In my experience, this is normally used for date/time columns to order something by the most recent time it appeared.