can we use ORDER BY in a Sub-query ?

zero · New User Joined: 01 Dec 2007 Posts: 21 Location: Hyderabad

Hi,
my table has data like this :

DEPT_NO emp_no
---------- ----------
D01 11111
D01 11111
D02 22222
D02 22222
D03 33333

My requirement is to get all the Departments with Maximum number of Employees.

for the above example, the output should be like this :

D01 2
D02 2.

I have written a query to achieve the same. It is working in DB2 for Windows. but, it is not working in SPUFI.

Craq Giegerich · Posted: Thu Mar 13, 2008 10:30 pm

What are the results when you do it in SPUFI?

Phrzby Phil · Posted: Thu Mar 13, 2008 10:46 pm

Your sub-query returns a count(*), so what exactly are you ordering?

Under what circumstances does it make sense to order any sub-query?

zero · New User Joined: 01 Dec 2007 Posts: 21 Location: Hyderabad

I am getting this error :

zero · New User Joined: 01 Dec 2007 Posts: 21 Location: Hyderabad

Here, My requirement is to get all the Departments with max. number of Employees. But, two depts. can have same maximum number of employees.

The work of the sub-query in this case is to find the Maximum number of Employees.

In this example, it is 2.

dick scherrer · Posted: Fri Mar 14, 2008 9:47 pm

Hello,

I believe we understand the requirement.

I also believe that you did not understand the -199 as well as the earlier reply - as coded, the order by is invalid.

Did you run the spufi test as requested? If so, what happens?

Gijz · Posted: Mon Mar 17, 2008 11:56 pm

Hello zero,

You can use ORDER BY in a subquery, but not in conjunction with an aggregate function in that subselect or a group by.

Possible solution to your problem? Use a common table expression (V8 onwards):

Srihari Gonugunta · Posted: Tue Mar 18, 2008 10:46 am

I suppose ORDER BY and FETCH FIRST clause can't be used together.

dbzTHEdinosauer · Posted: Tue Mar 18, 2008 12:39 pm

in vs8 yes

zero · New User Joined: 01 Dec 2007 Posts: 21 Location: Hyderabad

Thank you for your help...

sainathvinod · New User Joined: 01 Apr 2008 Posts: 11 Location: Chennai

Hey, you are getting the error -199 because you are not allowed to use the FETCH FIRST clause in a subquery. It can be used only in the main query as a last statement. There is nothing wrong in using the order by clause in the subquery. You can also use the combination of Order by and fetch first in the main query.

sainathvinod · New User Joined: 01 Apr 2008 Posts: 11 Location: Chennai

Please try the below query. I hope that this solves your problem :-

SELECT DEPT_NO
,COUNT(*)
FROM DEPT
GROUP BY DEPT_NO
HAVING COUNT(*) =
(SELECT MAX(TEMP.A) FROM
(SELECT DEPT_NO
,COUNT(*) A
FROM DEPT
GROUP BY DEPT_NO) TEMP)
WITH UR;

Gijz · Posted: Wed Apr 02, 2008 4:15 pm