Sqlite limit repeated rows with same id

user_3522182 0
Viewed 147

I'm using android SQLite database and I have the result below:

 id | root_id | parent_id   |name
----------------------------------
613 | null    | null        | m1
612 | null    | null        | m4
570 | null    | null        | m1
635 | 570     | 570         | m6
653 | 570     | 635         | m1
652 | 570     | 635         | m3
632 | 570     | 570         | m9
392 | null    | null        | m2
753 | 392     | 392         | m5
751 | 392     | 392         | m4
391 | null    | null        | m7

Im getting this result with the query below:

WITH RECURSIVE all_employees(id, root_id, parent_id, name) AS (
    SELECT id, id AS root_id, parent_id, name FROM employees WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, (CASE WHEN c.parent_id IS NOT NULL THEN p.root_id END), c.parent_id, c.name FROM employees c JOIN all_employees p ON p.id = c.parent_id ORDER BY id DESC
)

SELECT id, (CASE WHEN id != root_id THEN root_id ELSE NULL END) root_id, parent_id, name FROM all_employees

I want to set a limit for repeated rows with the same root_id, for example: Loading 2 rows with root_id 570 and 1 row with root_id 392:

 id | root_id | parent_id   |name
----------------------------------
613 | null    | null        | m1
612 | null    | null        | m4
570 | null    | null        | m1
635 | 570     | 570         | m6
653 | 570     | 635         | m1
392 | null    | null        | m2
753 | 392     | 392         | m5
391 | null    | null        | m7
android-sqlite common-table-expression sqlite android database
3 Answers

Union of limited selects

WITH RECURSIVE all_employees(id, root_id, parent_id, name) AS (
    SELECT id, id AS root_id, parent_id, name 
    FROM employees 
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, (CASE WHEN c.parent_id IS NOT NULL THEN p.root_id END), c.parent_id, c.name 
    FROM employees c 
    JOIN all_employees p ON p.id = c.parent_id ORDER BY id DESC
)
SELECT id, root_id, parent_id, name
FROM all_employees
where root_id not in (570, 392)
union all
select *
from (
   select *
   from all_employees
   where root_id =570 
   limit 3
)
union all
select *
from (
   select *
   from all_employees
   where root_id =392
   limit 2
)
user_62199790

Create another CTE:

counters(root_id, n) AS (VALUES (570, 2), (392, 1))

where you return all the root_ids that you want to restrict their rows and the number of rows for each one and then use a LEFT join of the recursive CTE to that.
Finally, set the condition in the WHERE clause with a correlated subquery:

WITH 
  RECURSIVE all_employees(id, root_id, parent_id, name) AS (
    SELECT id, id AS root_id, parent_id, name 
    FROM employees 
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, (CASE WHEN c.parent_id IS NOT NULL THEN p.root_id END), c.parent_id, c.name 
    FROM employees c JOIN all_employees p 
    ON p.id = c.parent_id 
  ),
  counters(root_id, n) AS (VALUES (570, 2), (392, 1))
SELECT a.id, (CASE WHEN a.id <> a.root_id THEN a.root_id END) root_id, a.parent_id, a.name
FROM all_employees a LEFT JOIN counters c
ON c.root_id = a.root_id
WHERE c.root_id IS NULL 
   OR (SELECT COUNT(*) FROM all_employees b WHERE b.root_id IS a.root_id AND b.id <= a.id) <= c.n

Note that the ORDER BY clause inside the CTE is useless, because it is not guaranteed that the rows will be returned in that order when you select from the CTE.
You can set the order of the rows that you want in the final query.

user_104988280

Add a counter to the CTE and then use WHERE logic for the filtering:

WITH RECURSIVE all_employees(id, root_id, parent_id, name, lev) AS (
    SELECT id, id AS root_id, parent_id, name, 0 as lev
    FROM employees
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id,
           (CASE WHEN c.parent_id IS NOT NULL THEN p.root_id END), c.parent_id, c.name, p.lev + 1
    FROM employees c JOIN
         all_employees p
         ON p.id = c.parent_id
    ORDER BY id DESC   -- don't think this does anything
   )
SELECT id, (CASE WHEN id <> root_id THEN root_id END) as root_id, parent_id, name
FROM all_employees
WHERE root_id = 570 AND cnt <= 1 OR
      root_id = 392 AND cnt <= 2 OR
      root_id NOT IN (570, 392);

The point here is to put the count in the recursive CTE and then use that information for filtering. You can phrase the WHERE clause however you prefer:

WHERE (CASE WHEN root_id = 570 THEN cnt <= 1
            WHEN root_id = 392 THEN cnt <= 2
            ELSE 1
       END)
user_11440350
Related