You may get the following error when you combine DISTINCT and ORDER BY clauses in a query as follows:
select distinctThe problem is caused by the presence of column view_date in the ORDER BY clause that is the expression mentioned in the error message. The term expression is probably used because one can specify expressions in the ORDER BY, not just simple columns.
order by view_date;
ORA-01791: not a SELECTed expression
The query indeed will run without problems if the ORDER BY clause contains SELECTed columns or expressions:
order by application_id;
select distinctNote that it's also possible to specify expressions based on the SELECTed columns that are not explicitly calculated in the SELECT list:
order by application_id, trunc(view_date);
order by upper(application_name), application_id, trunc(view_date);
But in the end, is either of these alternate queries returning an acceptable result as far as your application is concerned?
How can i turn the original (illegal) query into an acceptable form?
Over the years I've seen people using the DISTINCT clause quite arbitrarily and most of them had an MS-Access background...
Don't ask me if the two things were correlated ;-)
It looks like they just want to be sure that there are no duplicates in the resulting recordset, so they add DISTINCT even if they don't really need to do that. If that is the case, then the solution is easy, just get rid of this keyword.
However the query above seems to be an illegal representation of a legitimate (albeit strange) requirement:
i'd like to retrieve unique pairs of application IDs and user agents in order of appearance in the log.
If this is the correct interpretation, then DISTINCT is useless and GROUP BY is the answer.
select agent, application_idIn conclusion I'd say that ORA-01791 is either the symptom of a trivial error or the ringing bell of an ill-designed query.
group by agent, application_id)
order by d;
See message translations for ORA-01791 and search additional resources.