On 1/03/2020 22:57, mailing lists wrote:
> Assume I create the following table:
>
> CREATE TABLE Test (ID INTEGER PRIMARY KEY, Value TEXT);
> INSERT INTO Test (Value) VALUES('Alpha');
> INSERT INTO Test (Value) VALUES('Beta');
> INSERT INTO Test (Value) VALUES('Beta');
> INSERT INTO Test (Value) VALUES('Alpha');
>
> According to the documentation of group_concat the order is undefined, indeed:
>
> SELECT group_concat(DISTINCT Value) FROM Test ORDER BY Value ASC;
> SELECT group_concat(DISTINCT Value) FROM Test ORDER BY Value DESC;
>
> Both queries result in Alpha,Beta.
>
> Changing the queries to
>
> WITH Result(x) AS (SELECT DISTINCT Value FROM Test ORDER BY Value ASC) SELECT group_concat(x) FROM Result;
> WITH Result(x) AS (SELECT DISTINCT Value FROM Test ORDER BY Value DESC) SELECT group_concat(x) FROM Result;
>
> leads to the results Alpha,Beta, respectively Beta,Alpha.
>
> Is this a coincidence or is this guaranteed to work?
>
> Are there any other solutions / possibilities?
I also sometimes need a deterministic version of group_concat(). For
such cases, I have written the short UDF below (in Python, but I guess
the algorithm can easily be translated in other languages):
JLH
class group_concat2:
# Rewriting of "group_concat" of SQLite to simulate that of MySQL.
# Implements "distinct", "order by", "descending" and "separator".
# Interprets "null" values "intuitively"
#
# Format: group_concat2(value,distinct,sortkey,direction,separator)
# value: char or numeric SQL expression; if numeric,
converted into char;
# the next value to concatenate;
# discarded if None (Python translation of SQL null).
# distinct: numeric or char SQL expression; if char, converted
into integer;
# uniqueness indicator;
# if 1, duplicates ignored; if 0, duplicates allowed.
# sortkey: char or numeric SQL expression (no conversion);
# the order key value for the current "value" instance;
# If None or u'', the current "value" instance is used
instead.
# direction: numeric or char SQL expression; if char, converted
into integer;
# ordering direction (1 = asc; 2 = desc).
# sep: char or numeric SQL expression; if numeric,
converted into char;
# value separator;
# If None, = default u','.
# Example:
# select City,group_concat2(lower(CustID),1,Account,'2','; ') as
Customers
# from CUSTOMER group by City;
def __init__(self):
# Initialize
self.number = 0 # number of values added
self.valList = [] # List of values to concatenate
self.orderby = [] # list of values of the order key
self.distinct = 0 # whether "valList" values must be unique
(0 = no; 1 = yes)
self.direction = 1 # ordering direction (1 = asc; 2 = desc)
self.sep = u',' # separator
def step(self,value,distinct,sortkey,direction,sep):
# Adding a new value to concatenate.
# Each call of this method may specify different values of
# (distinct,sortkey,direction,sep) parameters.
# However, only those specified by the call of the first
"value" instance
# will be considered, the others being ignored.
import numbers
self.number += 1
# Initialize user values of "distinct", "direction" and "sep"
if self.number == 1:
if distinct in [1,u'1']:
self.distinct = 1
if direction in [1,2,u'1',u'2']:
self.direction = int(direction)
if sep is not None:
if isinstance(sep,numbers.Number):
self.sep = unicode(sep)
else:
self.sep = sep
if sortkey is None:
sortkey = value if value is not None else 1
elif sortkey == u'':
sortkey = value if value is not None else 1
if value is not None:
if isinstance(value,numbers.Number):
value = unicode(value)
if self.distinct:
if value not in self.valList:
self.valList.append(value)
self.orderby.append(sortkey)
else:
self.valList.append(value)
self.orderby.append(sortkey)
else:
# value discarded
pass
def finalize(self):
if self.direction == 1:
self.valList = [y for x,y in
sorted(zip(self.orderby,self.valList),reverse=False)]
else:
self.valList = [y for x,y in
sorted(zip(self.orderby,self.valList),reverse=True)]
return self.sep.join(self.valList)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Locked
