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

Question

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.

Accepted Answer

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?

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

Solution

Problem