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