How can I merge results from two SELECTs?

(1) By PazO (JohnSmith) on 2021-05-25 14:29:06 [link]

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 <b>one result per parameter-id</b> with the following logic: <br>
If parameters exists in snapshot-2 then return this value,<br>
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,<br>
PazO

(2) By Igor Tandetnik (itandetnik) on 2021-05-25 14:41:44 in reply to 1

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]

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]

If the real schema is as simple as the example you could use SQLite's [bare columns in aggregates](https://www.sqlite.org/quirks.html#aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause) 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]

Thank you very much

This is working