CODEWITHSUNDEEP
sqloracle-databasesql-order-bydistinct
user2855120
user2855120

Reputation: 21

Order By in Oracle cause ORA-01791: not a SELECTed expression

I have the below query. When executed it throws an error and the query is not executed.

SELECT DISTINCT svc.column1 ,
  svc.column2 ,
  svc.column3 ,
  svc.column4 ,
  svc.column5 ,
  svc.column6 ,
  svc.column7 ,
  svc.column8 ,
  svc.column9 ,
  svc.column10 ,
  svc.column11 ,
  svc.column12
FROM shemaName.tableName svc
WHERE svc.column13 IS NOT NULL
AND svc.column14    = 'DEEPAK'
AND svc.column15    = '188888'
AND ROWNUM         <=10
AND column16        = 'N'
ORDER BY svc.column13;

And the error I got was

java.sql.SQLSyntaxErrorException: ORA-01791: not a SELECTed expression.

The query returns result when order by clause is removed, as below:

SELECT DISTINCT svc.column1 ,
  svc.column2 ,
  svc.column3 ,
  svc.column4 ,
  svc.column5 ,
  svc.column6 ,
  svc.column7 ,
  svc.column8 ,
  svc.column9 ,
  svc.column10 ,
  svc.column11 ,
  svc.column12
FROM shemaName.tableName svc
WHERE svc.column13 IS NOT NULL
AND svc.column14    = 'DEEPAK'
AND svc.column15    = '188888'
AND ROWNUM         <=10
AND column16        = 'N';

What is wrong in adding order by clause?

Upvotes: 2

Views: 5840

Answers (3)

Nallamachu
Nallamachu

Reputation: 1488

One simple mistake you have done was, svc.column13 column not defined in the DISTINCT COLUMNS. So, the engine was not treating the statement as SELECT expression.

Upvotes: 0

APC
APC

Reputation: 146219

The DISTINCT filters out duplicate values of columns 1-12. So the result set does not match 1:1 with the rows in your table.

Now your ORDER BY clause asks the database to sort the result set using a column which is not in the result set. But there may be multiple values of column13 for each combination of columns 1-12. The database does not - cannot - know which one to use, so it hurls ORA-01791.

As to a solution, you need a way to expose column13 to the database without including it in the final projection. So probably you need to use an in-line of some sort. Which approach to take depends on your data.

If column13 has only one value for each combination of columns 1-12 then you can use an in-line view like this:

SELECT q.column1 ,
  q.column2 ,
  q.column3 ,
  q.column4 ,
  q.column5 ,
  q.column6 ,
  q.column7 ,
  q.column8 ,
  q.column9 ,
  q.column10 ,
  q.column11 ,
  q.column12
FROM ( SELECT DISTINCT svc.column1 ,
              svc.column2 ,
              svc.column3 ,
              svc.column4 ,
              svc.column5 ,
              svc.column6 ,
              svc.column7 ,
              svc.column8 ,
              svc.column9 ,
              svc.column10 ,
              svc.column11 ,
              svc.column12 ,
              svc.column13
        FROM shemaName.tableName svc
        WHERE svc.column13 IS NOT NULL
        AND svc.column14    = 'DEEPAK'
        AND svc.column15    = '188888'
        AND ROWNUM         <=10
        AND column16        = 'N' 
      ) q
order by q.column13;

If there are multiple values for column13 choose some restricting criterion and aggregate on that in an in-line view. This one chooses the lowest value of column13:

SELECT q.column1 ,
  q.column2 ,
  q.column3 ,
  q.column4 ,
  q.column5 ,
  q.column6 ,
  q.column7 ,
  q.column8 ,
  q.column9 ,
  q.column10 ,
  q.column11 ,
  q.column12
FROM ( SELECT svc.column1 ,
              svc.column2 ,
              svc.column3 ,
              svc.column4 ,
              svc.column5 ,
              svc.column6 ,
              svc.column7 ,
              svc.column8 ,
              svc.column9 ,
              svc.column10 ,
              svc.column11 ,
              svc.column12 ,
              min(svc.column13) as column13
        FROM shemaName.tableName svc
        WHERE svc.column13 IS NOT NULL
        AND svc.column14    = 'DEEPAK'
        AND svc.column15    = '188888'
        AND ROWNUM         <=10
        AND column16        = 'N' 
        group by svc.column1 ,
                  svc.column2 ,
                  svc.column3 ,
                  svc.column4 ,
                  svc.column5 ,
                  svc.column6 ,
                  svc.column7 ,
                  svc.column8 ,
                  svc.column9 ,
                  svc.column10 ,
                  svc.column11 ,
                  svc.column12 
      ) q
order by q.column13;

Alternatively, just include column13 in the DISTINCT projection and accept the multiplication of values.

Upvotes: 1

Florin Ghita
Florin Ghita

Reputation: 17643

The engine cannot process your query because it defies logic. I'll give you an lighter example to understand easier the case:

Table
colA  colB
1     10
1     30
2     20
2     20

So:

 select distinct colA from table;

1
2

select colA from table order by colb;

1
2
2
1

BUT, select distinct colA from table order by colb; what shoud give? the value 1 should be first and last in the same time. This is an impossible problem.

PS: In your case it may be appropriate to add column13 to the select distinct clause. The query will run fine in that case.

Upvotes: 3

Related Questions