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

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.

sqloraclehibernateoracle11g


Solution 1:[1]

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

Solution 2:[2]

When using DISTINCT in a query that has an ORDER BY you must select all the columns you've used in the ORDER BY statement:

SELECT DISTINCT share.rooms.floors.floorOrder, share.rooms.roomNumber,
         share.rooms.firstEffectiveAt, share.shareNumber, share.sharePercent
...
ORDER BY share.rooms.floors.floorOrder, share.rooms.roomNumber,
         share.rooms.firstEffectiveAt, share.shareNumber, share.sharePercent

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Matthew Zackschewski
Solution 2 Boško Bezik

Related Questions