On Tue, 12 Feb 2019 14:08:20 +0200
R Smith <[email protected]> wrote:
> The semantics are made more legible using a CTE here, but can be
> done without it. The essential difference is that it uses a sub-query
> to obtain the minimum priority as opposed to introducing a sub-table
> sort.
Thank you for your reply!
The CTE example has been a learning experience. I have played around
some more and produced a solution for the general "every source_id has
its own priority" case, which I'm including in case someone else finds
this thread:
WITH
RANKED(id, value, rk) AS (
SELECT id, value, CASE source_id
-- source_id =then=> rank
when 3 then 1
when 1 then 2
when 2 then 3
END
FROM test
)
SELECT id, value
FROM RANKED AS test1
WHERE
test1.id IN (1,2)
AND test1.rk = (
SELECT MIN(rk)
FROM RANKED AS test2
WHERE test2.id = test1.id
)
;
This produces the same query plan as your query. Omitting a WHEN allows
me to completely ignore a source_id, should I want that. With
RANKED(id, value, rk) AS (
SELECT id, test.value, RANKING.rowid
FROM test
JOIN carray($ptr,$n,$type) AS RANKING
ON test.source_id = RANKING.value
)
I can supply a list of source_ids in order of decreasing priority from
an array in my application, though I lose cross-database portability
(is there a portable way to pass an array to a parameterized query,
though?) and may lose some of the performance to the JOIN (didn't
build a sample application to run EXPLAIN QUERY PLAN, sorry).
--
Best regards,
Ivan
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
