How to get row number without windows functions in SQLite?

Question

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?

Answer 1

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;

• yaugenka: This is the one I was looking for! Just the last order by should be changed to ORDER BY name='b', name and it works perfect. Thanks you!

Answer 2

SQLite have no STRING_AGG() function.

SELECT t1.id, t1.name, COALESCE(GROUP_CONCAT(t2.name), '') children_names
FROM tbl t1
LEFT JOIN tbl t2 ON t1.id = t2.pid
WHERE t1.pid = 0
GROUP BY t1.id, t1.name;

fiddle

GROUP_CONCAT() have no ORDER BY clause. No way to affect on the concatenated values order.

PS. Of course you may try to emulate ordered concatenation using recursive CTE with concatenating values one-by-one in the order which you need, but this CTE will be too complex and, I think, this makes no sense.

PPS. If you need to get ROW_NUMBER() without window function by some field/expression which is unique you may use this trick.

• yaugenka: is there a way to make b values apear at the end of the list with this technique?
• Akina: @yaugenka Yes. One more trick - absolutely unsupportable.
• yaugenka: wow that's becoms too complicated. There is a simpler solution from CL below.