Search code examples
db2db2-luw

Correlated subquery in order by clause in DB2

I had a query similar to the following and was wondering that DB2 complained about the correlation use in the ORDER BY clause. It errored with something like

[42703][-206] "A.ID" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703

I was able to rewrite the query to avoid the correlation usage but I couldn't find a reference in the documenation about this. Is this a bug or am I just not able to find details on the expected behavior?

SELECT a.id
FROM A a
ORDER BY (
  SELECT COUNT(*)
  FROM   B b
  WHERE  b.id = a.id
)
Solution

  • You can't use correlated query in order by clause. However there is many ways to get same result, for example

    select count(*) as count_num ,a.ID 
from
   a join  b on  a.ID=b.ID
GROUP BY a.ID
order by 1 DESC