Help with SQLite query to generate new table based on lookups

Question

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

Answer 1

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