Using the DISTINCT keyword causes this error: not a SELECTed expression

59
votes

I have a query that looks something like this:

SELECT DISTINCT share.rooms
FROM Shares share
  left join share.rooms.buildingAdditions.buildings.buildingInfoses as bi
... //where clause omitted
ORDER BY share.rooms.floors.floorOrder, share.rooms.roomNumber,
         share.rooms.firstEffectiveAt, share.shareNumber, share.sharePercent

Which results in the following exception:

Caused by: org.hibernate.exception.SQLGrammarException: ORA-01791: not a SELECTed expression

If I remove the DISTINCT keyword, the query runs without issue. If I remove the order by clause, the query runs without issue. Unfortunately, I can't seem to get the ordered result set without duplicates.

2
sqloraclehibernateoracle11g
Thanks @Lamak for the response. It isn't clear to me why the DISTINCT causes the db to ignore the other columns, as it doesn't ignore them without the DISTINCT. However, given that it matters, how do I get the ordered, duplicate free result set? - Ken
then how do you want them to be ordered?. If you want duplicate free results for the column share.rooms, then you need to understand that since the same room may have different values for floorOrder or roomNumber or any other column, how do you want them to be ordered?, by the min value of those?, the max?. - Lamak
I see now. Thanks again. - Ken
As a side note: I come here because of a similar issue, happened I forgot an id column (I used for debugging) in the order by clause - jean

2 Answers

99
votes

You are trying to order your result with columns that are not being calculated. This wouldn't be a problem if you didn't have the DISTINCT there, but since your query is basically grouping only by share.rooms column, how can it order that result set with other columns that can have multiple values for the same share.rooms one?

8
votes

This post is a little old but one thing I did to get around this error is wrap the query and just apply the order by on the outside like so. 

SELECT COL
FROM (
   SELECT DISTINCT COL, ORDER_BY_COL
   FROM TABLE
   // ADD JOINS, WHERE CLAUSES, ETC.
) 
ORDER BY ORDER_BY_COL;

Hope this helps :)