How to fill missing numbers with Null?-CodePudding

In SQLite how to fill in null for missing numbers from a range of 1-10 when using Group By? I have a table called DataTest:

DataID	theData
1	50
2	38
2	48
4	38
5	48
8	39
9	50
9	60
10	90

If I do SELECT theData FROM DataTest GROUP BY dataID; I receive :

theData
50
48
38
48
39
60
90

How to alter the query to produce rows 3, 6 and 7 as below which were originally missing?

theData
50
48
null
38
48
null
null
39
60
90

CodePudding user response：

One option uses a recursive query to enumerate all numbers between the lower and upper bound of the table, then computes the aggregates:

with recursive ids (id, maxid) as (
    select min(dataid), max(dataid) from mytable
    union all
    select id   1, maxid from ids where id < maxid
)
select i.id, max(t.thedata) max_data
from ids i 
left join mytable t on t.dataid = i.id
group by i.id