As per SQL standards we cannot use ORDER BY clause in subqueries. It also does not make sense to use ORDER BY inside a derived table.Let's see an SQL example to identify the problem and how to overcome it.
This is a perfectly fine query:
SELECT E_EMPNO,E_EMPNAME,E_MOB FROM EMPLOYEE
ORDER BY 1,2;
Now if we create a derived table using this query it will fail.
SELECT COUNT(1) FROM
(
SELECT E_EMPNO,E_EMPNAME,E_MOB FROM EMPLOYEE
ORDER BY 1,2
)TBL1;
Error–> 3706: Syntax error: ORDER BY is not allowed in subqueries.
Possible Solutions:
1) Now the obvious solution could be to remove ORDER BY clause from the query. However this may not be possible in all the cases. Like you have a java utility which performs various operations on any given query. The query is input by users and the query goes through various operations. Once such operation is to COUNT the rows impacted by the query. Simple answer could be to keep the query as it is and create a derived table and then use COUNT function to check rows impacted from that query. So this solution may not work for us because the query has ORDER BY clause in it.
2) Run the query as it is and get the RESULTSET in java. Now run the while loop and increment counter and then print the value of counter variable.
int cnt = 0;
while(localResultSet1.next()){cnt++;}
System.out.println("Number of rows :" + cnt);
This is definitely a very bad approach as it may take memory to hold the resultset. Also it will take time to run the loop if output has billions & billions of rows.
3) Use COUNT OLAP function in the query itself as last column and then use TOP or SAMPLE to restrict the number of rows in the output.
So Query will look like :
SELECT E_EMPNO,E_EMPNAME,E_MOB,COUNT(1) OVER(ORDER BY 1) AS CNT_FINAL FROM EMPLOYEE
ORDER BY 1,2
SAMPLE 1;
Now run the query in JAVA and hold it in result set. It will have only 1 record. Fetch the value of CNT_FINAL column:
localResultSet1.next();
System.out.println("Number of rows :" + localResultSet1.getInt("CNT_FINAL"));
In this way you can calculate COUNT of rows impacted by any query and overcome the error caused by ORDER BY clause in it.