Using LIST and sorting in Sybase IQ - multiple LIST selects throws off "ORDER BY"?

Question

My query does something like...

LIST( column1, " ;" ORDER BY column2)

This works. When I add more LIST functions in my SELECT then the ORDER BY is ignored. Anyone know why that might be occurring? I'd like to avoid doing unions or running the queries separately then joining

score 3 · Accepted Answer · answered Jun 10 '16 at 07:58

3

The documentation suggests that there are restrictions on the use of multiple ORDER BY clauses:

"A query block containing more than one aggregate function with valid ORDER BY clauses can be executed if the ORDER BY clauses can be logically combined into a single ORDER BY clause."

answered Jun 10 '16 at 07:58

RobV

2,263
1
11
7

Ah ok. Annoying. Thanks for confirming – cpd1 Jun 11 '16 at 12:58
the documentation is not clear to me - it should say something to the effect that: while multiple list() expressions are allowed, the "order by" on the second and all subsequent list() expressions will be ignored and will revert to the order by of the first list() expression – spioter Jul 03 '22 at 14:35

spioter · Answer 2 · 2022-07-03T16:50:19.793

Even as of SAP IQ 16.1.4, my tests show that if a query has multiple list() expressions, one "order by" governs all the rest.

However, there is a workaround: one list per subquery, join the subqueries back together

-- work around - contrived example but shows that you can calculate lists in separate sub-query and join them together
select *
from (   select 
          creator
          , list (table_name, ',' order by table_name) TableNameSort
   from sys.sysTab -- describe  sys.sysTab 
   where sys.sysTab.table_name like 'sys%'
   group by creator
) vw_sort1
cross join (
   select creator
          , list (table_name, ','  order by object_id) ObjectIDSort
   from sys.sysTab -- describe  sys.sysTab 
   where sys.sysTab.table_name like 'sys%'
   group by creator
) vw_sort2

The following clearly shows the order by is not respected on second list

-- Example of single List sorting correctly by TableName
   select 
          creator
          , list (table_name, ',' order by table_name) TableNameSort
          , 'Correct TableName Sort' as TestResult
   from sys.sysTab -- describe  sys.sysTab 
   where sys.sysTab.table_name like 'sys%'
   group by creator

-- return both lists, but sort is identical, the second List sort is applied to both
   select creator
          , list (table_name, ',' order by table_name) TableNameSort
          , list (table_name, ','  order by object_id) ObjectIDSort
          , 'Both Sorted by ObjectID' as TestResult
   from sys.sysTab -- describe  sys.sysTab 
   where sys.sysTab.table_name like 'sys%'
   group by creator


-- return both lists, but sort is identical, the second List sort is applied to both
   select creator
          , list (table_name, ','  order by object_id) ObjectIDSort
          , list (table_name, ',' order by table_name) TableNameSort
          , 'Both Sorted by TableName' as TestResult
   from sys.sysTab -- describe  sys.sysTab 
   where sys.sysTab.table_name like 'sys%'
   group by creator

-- Example of single List sorting correctly by ObjectID
   select creator
          , list (table_name, ','  order by object_id) ObjectIDSort
          , 'Correct ObjectID Sort' as TestResult
   from sys.sysTab -- describe  sys.sysTab 
   where sys.sysTab.table_name like 'sys%'
   group by creator

Using LIST and sorting in Sybase IQ - multiple LIST selects throws off "ORDER BY"?

2 Answers2