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.

Answer 1

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?

Answer 2

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 :)