Hi!
I have a table of some values obtained from different sources:
create table test (
id, source_id, value,
primary key(id, source_id)
);
insert into test values
(1, 1, 11), (1, 2, 12), (1, 3, 13),
(2, 1, 21),
(3, 2, 32);
When SELECTing the values, I would like to filter by id and get only
one value per id. This part is misleadingly easy:
select * from test where id in (1,2) group by id;
but then I don't get to control which source I'm obtaining the values
from (when there is more than one). Let's assume for now that I prefer
to choose values with a particular source_id, but if those are not
present, I would take what's available. I managed to invent a query
which would assign a priority to each value using window functions:
select
*, row_number() over win
from test
where
id in (1,2)
window win as (
partition by id
order by abs(source_id-3)
);
By subtracting a different value in the ORDER BY ABS(...) clause, I can
force a value with a different source_id to come up first.
And since SQLite doesn't currently allow putting window functions in the
WHERE clause, I'm using a nested query to actually get the values with
the highest priority:
select id, value from (
select
id, value, row_number() over win as priority
from test
where
id IN (1,2)
window win as (
partition by id
order by abs(source_id-3)
)
) where priority = 1;
which results in the following query plan:
QUERY PLAN
|--CO-ROUTINE 1
| |--CO-ROUTINE 3
| | |--SEARCH TABLE test USING INDEX sqlite_autoindex_test_1 (id=?)
| | `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
| `--SCAN SUBQUERY 3
`--SCAN SUBQUERY 1
Is this the most effective way to express my query? Can the more general
problem of assigning a priority to all sources (e.g. "I want records
from source_id 3, otherwise 1, otherwise 2") be solved in a similar way?
--
Best regards,
Ivan
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
