Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.8k views
in Technique[技术] by (71.8m points)

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

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...