How can I merge results from two SELECTs?
(1) By PazO (JohnSmith) on 2021-05-25 14:29:06 [link] [source]
Hello,
Assuming I have the following table:
CREATE TABLE SnapshotParameters(
snapshot_id integer,
param_id integer,
param_value integer
);
In this table I hold the following data:
-- Original snapshot (values are 1)
INSERT INTO SnapshotParameters (snapshot_id, param_id, param_value) VALUES ( 1, 1, 1);
INSERT INTO SnapshotParameters (snapshot_id, param_id, param_value) VALUES ( 1, 2, 1);
INSERT INTO SnapshotParameters (snapshot_id, param_id, param_value) VALUES ( 1, 3, 1);
INSERT INTO SnapshotParameters (snapshot_id, param_id, param_value) VALUES ( 1, 4, 1);
INSERT INTO SnapshotParameters (snapshot_id, param_id, param_value) VALUES ( 1, 5, 1);
-- Edited snapshot (values are 2)
INSERT INTO SnapshotParameters (snapshot_id, param_id, param_value) VALUES ( 2, 3, 2);
INSERT INTO SnapshotParameters (snapshot_id, param_id, param_value) VALUES ( 2, 4, 2);
Now I want to run a query that will return to me one result per parameter-id with the following logic:
If parameters exists in snapshot-2 then return this value,
Otherwise - return value from snapshot-1.
Result example:
1, 1, 1
1, 2, 1
2, 3, 2
2, 4, 2
1, 5, 1
Thanks for any tip,
PazO
(2) By Igor Tandetnik (itandetnik) on 2021-05-25 14:41:44 in reply to 1 [source]
Something along these lines:
select ifnull(t2.snapshot_id, t1.snapshot_id), t1.param_id, ifnull(t2.param_value, t1.param_value)
from SnapshotParameters t1 left join SnapshotParameters t2 on (
t1.param_id = t2.param_id and t2.snapshot_id=2)
where t1.snapshot_id=1
(3) By Rado (antlor) on 2021-05-25 14:43:27 in reply to 1 [link] [source]
select a.snapshot_id, a.param_id, a.param_value from SnapshotParameters a where not exists(select 1 from SnapshotParameters b where b.param_id = a.param_id and b.snapshot_id = 2) and a.snapshot_id = 1 union all select a.snapshot_id, a.param_id, a.param_value from SnapshotParameters a where a.snapshot_id = 2 order by 2
(4) By David Raymond (dvdraymond) on 2021-05-25 15:01:27 in reply to 1 [link] [source]
If the real schema is as simple as the example you could use SQLite's bare columns in aggregates to get the highest snapshot for each parameter.
Something like...
select max(snapshot_id) as snapshot_id, param_id, param_value
from SnapshotParameters
group by param_id
order by param_id;
(5) By PazO (JohnSmith) on 2021-05-26 06:32:14 in reply to 2 [link] [source]
Thank you very much
This is working