New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Order LIST'ed records according to order-by clause [CORE2332] #2756
Comments
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 |
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. |
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. |
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 |
Commented by: Cosmin Apreutesei (cosmin_ap2) Just found out about the GROUP_CONCAT operator of mysql [1]. So how'bout LIST([DISTINCT] expr [,expr ...] :) [1] http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat |
Commented by: Dany Marmur (dany) I am currently solving this problem using a CTE like so: WITH EDU_EPT AS ( 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. |
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. |
Commented by: Kjell Rilbe (kjellrilbe) Any news about this? Any new operations in Firebird that adds other (documented) approaches to achieve the same thing? |
yes indeed some news on 'ORDERED_LIST()' function would be great to hear :) another work around for smaller datasets: ... quite tedious and not very scalable ... |
Don't this cover your requirements?
|
The SQL:2016 standard, section 10.9 <aggregate function> defines |
Sadly LISTAGG is not available in LO Firebird (embedded) :( However; "select list(c) does work! (of course, duh) - cheers! - use trim to remove spaces in list, ie select TRIM(list(c)) [case closed - Love you Libre!] |
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.
The text was updated successfully, but these errors were encountered: