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

sql oracle hibernate oracle11g
Ken picture Ken · Mar 7, 2013 · Viewed 65.2k times · Source

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

Lamak picture Lamak · Mar 7, 2013

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?

Related questions

Exception while writing to the Database (org.hibernate.exception.ConstraintViolationException)

I have a file(this file actually has tab separated values) which has to be written into one of the database tables. This file can contain duplicate entries as well. I am processing the records in the file in set …

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More