CODEWITHSUNDEEP
sqlsqlitecount
AndrewBloom
AndrewBloom

Reputation: 2408

Query with WITH clause and COUNT subquery

In the query below, I don't get the results i would expect. Any insights why? How could i reformulate such query to get the desired results?

Schema (SQLite v3.30)

WITH RECURSIVE
  cnt(x,y) AS (VALUES(0,ABS(Random()%3)) UNION ALL SELECT x+1, ABS(Random()%3) FROM cnt WHERE x<10),
  i_rnd as (SELECT r1.x, r1.y, (SELECT COUNT(*) FROM cnt as r2 WHERE r2.y<=r1.y) as idx FROM cnt as r1)
SELECT * FROM i_rnd ORDER BY y;

result:

| x   | y   | idx |
| --- | --- | --- |
| 1   | 0   | 3   |
| 5   | 0   | 6   |
| 8   | 0   | 5   |
| 9   | 0   | 4   |
| 10  | 0   | 2   |
| 3   | 1   | 4   |
| 0   | 2   | 11  |
| 2   | 2   | 11  |
| 4   | 2   | 11  |
| 6   | 2   | 11  |
| 7   | 2   | 11  |

expected result:

| x   | y   | idx |
| --- | --- | --- |
| 1   | 0   | 5   |
| 5   | 0   | 5   |
| 8   | 0   | 5   |
| 9   | 0   | 5   |
| 10  | 0   | 5   |
| 3   | 1   | 6   |
| 0   | 2   | 11  |
| 2   | 2   | 11  |
| 4   | 2   | 11  |
| 6   | 2   | 11  |
| 7   | 2   | 11  |

In other words, idx should indicate how many rows have y less or equal than the y of row considered.

Upvotes: 1

Views: 508

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270371

I would just use:

select cnt.*,
       count(*) over (order by y)
from cnt;

Here is a db<>fiddle.

The issue with your code is probably that the CTE is re-evaluated each time it is called, so the values are not consistent -- a problem with volatile functions in CTEs.

Upvotes: 1

Related Questions