Help with SQLite query to generate new table based on lookups

Sorry, I can't think of a more specific title.

SQLFiddle here: http://sqlfiddle.com/#!5/cfed3/1/0

I have a BaseData table

Name
ParamId
NumericValue

A
202
30

B
203
69

O
203
50

A
203
70

I
204
2

B
204
1

O
204
1

A
204
1

J
205
2

F
205
1

• Where ParamId = 204 or 205, the NumericValue is a "ClusterNumber".

• Where ParamId = 202, the NumericValue is a "LowerThreshold".

• Where ParamId = 203, the NumericValue is an "UpperThreshold".

Now we need to create a cluster table where the ClusterNumber comes from the BaseData table where ParamId = 204 or 205, and we default the lower and upper threshold to 0 and 100:

ClusterNumber
LowerThreshold
UpperThreshold

1
0
100

2
0
100

Now we update the Lower and Upper thresholds. Take for example, the LowerThreshold (ParamId = 202), there is only one row, and it's name is A. To determine the ClusterNumber, we look for where A is where ParamId = 204 or 205, we see that it is ClusterNumber = 1. Thus, ClusterNumber 1 has LowerThreshold 30.

If there are multiple numbers for lower/upper thresholds, then the last one wins.
We should end up with this table:

ClusterNumber
LowerThreshold
UpperThreshold

1
30
70

2
0
100

I can't work out how to set the Lower and Upper thresholds. You can see my code here: http://sqlfiddle.com/#!5/cfed3/1/0

There is no need to create a Cluster table, you can get your desired result in a single query:

with Clusters as (
  select Name, NumericValue as ClusterNumber from BaseData
     where BaseData.ParamId in (204,205)
),
lowers as (
  select Clusters.ClusterNumber, Basedata.NumericValue 
  from Basedata join Clusters on Basedata.Name=Clusters.Name 
  where ParamId=202 
  group by ClusterNumber
  order by Basedata.rowid desc
 ),
uppers as (
  select Clusters.ClusterNumber, Basedata.NumericValue 
  from Basedata join Clusters on Basedata.Name=Clusters.Name 
  where ParamId=203 
  group by ClusterNumber
  order by Basedata.rowid desc
 )
select distinct ClusterNumber, 
       coalesce((select lowers.NumericValue from lowers where lowers.ClusterNumber=Clusters.ClusterNumber),0) as LowerThreshold, 
       coalesce((select uppers.NumericValue from uppers where uppers.ClusterNumber=Clusters.ClusterNumber),100) as UpperThreshold 
from Clusters
order by ClusterNumber;

I used some CTE's for clarity.
You can use it as a view or store it into a table.
Here is the fiddle: http://sqlfiddle.com/#!5/cfed3/48

StackExchange Database Administrators Q#323614, answer score: 2