Support for declarative ordering inside the LIST aggregate function [CORE2332]

[firebird-automations]

Submitted by: Cosmin Apreutesei (cosmin_ap2)

Votes: 18

The LIST() function could rock, if only you could control the order of elements. Right now I can't use it to display data since the result is unpredictable. I can't use it to compute a hash over a group for the same reason -- I don't know what to make of it. It's like giving me a chocolate bar and then taking it back after the first bite :) Please consider it.

[firebird-automations]

Commented by: Philip Williams (unordained)

I don't see a general improvement request for Oracle-like analytical / window functions, but I could swear I'd already seen one somewhere. That would cover what you want, with its "over (order by ...)" clause -- except with an aggregate function, list, which last time I worked with Oracle, had to be built as a new custom aggregate function. My personal favorite is still "first value".

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#SQLRF06174

[firebird-automations]

Commented by: @asfernandes

FB 3 will certainly support OVER () for current aggregate functions. It may also support OVER (PARTITION BY ...).

But it will probably don't support OVER (ORDER BY).

So I think if any improvement is tought about LIST, we should care about two way of do the same thing in the future.

[firebird-automations]

Commented by: Cosmin Apreutesei (cosmin_ap2)

It's not as big deal to control the order as it is to have _some_ meaningful order.

It's very important to make the result _predictable_ and _idempotent_ otherwise I can't compare two lists, group-by a list, hash a list, and I cannot display them (think of small enumerations) since they scramble all the time.

So I guess sorting to alphabetical order by default would solve most of the issues.

[firebird-automations]

Commented by: Cosmin Apreutesei (cosmin_ap2)

Since some ppl are watching this, I found out that LIST(DISTINCT) enforces a sort which solves half of the problems. That's just an implementation detail, not a documented guarantee, but it's ok for the moment.

Just keeping the thread warm :D

[firebird-automations]

Commented by: Cosmin Apreutesei (cosmin_ap2)

Just found out about the GROUP_CONCAT operator of mysql [1]. So how'bout

LIST([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val])

:)

[1] http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

[firebird-automations]

Commented by: Dany Marmur (dany)

I am currently solving this problem using a CTE like so:

WITH EDU_EPT AS ( SELECT EEPT2.TARGET_SWE FROM EDUCATION_EVENT_PR_TRGT EEPT2 WHERE EEPT2.EDUCATION_EVENT_ID = :EDU_EVENT_ID ORDER BY EEPT2.ORDINAL, http://EEPT2.ID ) SELECT LIST('• ' || EEPT.TARGET_SWE, ASCII_CHAR(13) || ASCII_CHAR(10)) || '.' FROM EDU_EPT EEPT

it seems to work. I hope it produces a predictable result as well.

*If* this generates a predictable result i would of course want it remain so in future releases.

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

description: The LIST() function could rock, if only you could control the order of elements. Right now I can't use it to display data since the result is unpredictable. I can't use it to compute a hash over a group for the same reason -- I don't know what to make of it. It's like giving me a chocolate bar and then taking it back after the first bite :) Please consider it.

=>

The LIST() function could rock, if only you could control the order of elements. Right now I can't use it to display data since the result is unpredictable. I can't use it to compute a hash over a group for the same reason -- I don't know what to make of it. It's like giving me a chocolate bar and then taking it back after the first bite :) Please consider it.

[firebird-automations]

Commented by: Kjell Rilbe (kjellrilbe)

Any news about this? Any new operations in Firebird that adds other (documented) approaches to achieve the same thing?

[GNKNG]

yes indeed some news on 'ORDERED_LIST()' function would be great to hear :)

another work around for smaller datasets: add row-number partitioned by list headers and ITEMs ordered correctly as a sub-query, then use concat || and case(when/then/else) to LIST() the case(concatenation) ie
list [ (case when 'row=1' then ITEM else '') || (case when 'row=2' then ITEM else '') || (case when 'row=3' then ITEM else '') .... (case when 'row='some_not_too_big_number' then ITEM else '') ]

... quite tedious and not very scalable ...

[asfernandes]

Don't this cover your requirements?

select list(c)
    from (select c from t order by n);

[mrotteveel]

The SQL:2016 standard, section 10.9 <aggregate function> defines LISTAGG for this, where the WITHIN GROUP clause is used for sorting values.

[GNKNG]

Sadly LISTAGG is not available in LO Firebird (embedded) :(

However;

"select list(c) from (select c from t order by n); "

does work! (of course, duh) - cheers! - use trim to remove spaces in list, ie

select TRIM(list(c)) from (select c from t order by n);

[case closed - Love you Libre!]

[dyemanov]

Renamed the ticket to better suit the SQL:2023 implementation: LISTAGG (<value>) WITHIN GROUP (ORDER BY ...)