SQLite – How to Get Row Number Without Windows Functions

sqlite

Assume there is the following table schema and data

create table tbl(
    id integer primary key,
    name text,
    pid integer
);

insert into tbl
values
    (1,'d',0),
    (2,'c',1),
    (3,'b',1),
    (4,'e',1),
    (5,'b',0),
    (6,'a',0);

It is a one-level depth hierarchy of parent and child items.

I need to aggregate children names into their parents like so

 id | name | children_names
----+------+------------
  6 | a    |
  1 | d    | c, e, b
  5 | b    |

children names need to be sorted within each row and the entire result need to be sorted by name column alphabetically, but all b names must always go last.

In PostgreSQL I would do it with row_number() over() window function like this

with
t as (select * from tbl order by name),
t2 as (select * from t where name<>'b' union all select * from t where name='b'),
t3 as (select *, row_number() over() from t2)
select a.id, a.name, string_agg(b.name, ', ' order by b.row_number)
from t3 a left join t3 b on a.id=b.pid and a.id<>b.id
where a.pid=0
group by a.id, a.name, a.row_number
order by a.row_number

but I need this in Android Room which lacks window functions yet.

So how to get the same result without window functions used?

Best Answer

To sort group_concat() values, the original rows must come from an ordered subquery:

SELECT id,
       name,
       group_concat(cname) AS children_names
FROM (SELECT p.id,
             p.name,
             c.name AS cname
      FROM      tbl AS p
      LEFT JOIN tbl AS c ON p.id = c.pid
      WHERE p.pid = 0
      ORDER BY c.name = 'b',
               c.name)
GROUP BY id
ORDER BY name = 'b',
         name;
Related Question