Re: How to enforce a specific order of group_concat?

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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users