CODEWITHSUNDEEP
oracle-databaseviewsql-order-byora-00979
Morri
Morri

Reputation: 581

Oracle, ORDER BY with view results in error

I'm selecting data from a view with the following query

SELECT * FROM "SCH"."MyView"

If I add ORDER BY

SELECT * FROM "SCH"."MyView"
ORDER BY Name

I get the following error

[Err] ORA-00979: not a GROUP BY expression

Does the Oracle query engine translate this to an invalid query? How would one start troubleshooting something like this?

Here is a simplification of the "SCH"."MyView"

SELECT
    Name,
    (subquery) AS Foo,
    (subquery) AS Bar
FROM
    "SCH"."AnotherView"
GROUP BY 
    Name

If I remove the subqueries, I can use ORDER BY without errormessages. Subqueries are as follows:

( SELECT f.UnitPrice FROM "SCH"."AnotherView" f WHERE f.Name = main.Name AND f.Category = 'x' AND rownum < 2 ) AS priceX
( SELECT b.UnitPrice FROM "SCH"."AnotherView" b WHERE b.Name = main.Name AND b.Category = 'y' AND rownum < 2 ) AS priceY

Edit: if I add "WHERE rownum < 9999999", I get rid of the error. We're running version 10.2.0.4.0 64bit.

Upvotes: 0

Views: 1306

Answers (1)

user1127214
user1127214

Reputation: 3177

Maybe you tried to execute a SELECT statement that included a GROUP BY function (ie: MIN, MAX, SUM, COUNT) and an expression in the SELECT list that was not in the GROUP BY clause.

Upvotes: 0

Related Questions