I'm not the sharpest tool in the shed, here, but a couple of observations /
ideas. First, why in the ORDER clause use _3 and then "id" instead of _1?
Using one alias and not the other is inconsistent and could be confusing.
Personally, I would have did the following. Acquire the data using the CTE.
The select what is needed and order it from the CTE. Being script
generated, I find it really easy to create the primary select as a template
and insert the select within the CTE.
WITH data AS (
SELECT
"id" AS _1,
"json" AS _2,
json_extract(json, '$.foo') AS _3
FROM "testing"
)
SELECT *
FROM data
where _3 < 50
ORDER BY _3 DESC, _1
I read a lot about efficiency with sqlite here. I guess I've become
complacent about it. I find sqlite so fast that efficiency is distracting.
Just me.
dvn
On Sat, Mar 24, 2018 at 11:31 AM, Wout Mertens <[email protected]>
wrote:
> Hi list,
>
> I often have (autogenerated) queries like
>
> SELECT "id" AS _1,"json" AS _2 FROM "testing"
> WHERE json_extract(json, '$.foo') < 50
> ORDER BY json_extract(json, '$.foo') DESC,"id"
>
> where the json_extract(json, '$.foo') is indexed
>
> I wonder if it would be more efficient to write instead
>
> SELECT _1, _2 FROM (
> SELECT "id" AS _1,"json" AS _2, json_extract(json, '$.foo') AS _3 FROM
> "testing"
> WHERE _3 < 50
> ORDER BY _3 DESC,"id")
>
> , so aliasing the common expression? Or is that just extra work for SQLite?
>
> If I did this, I would want to do it always, also when I'm using the
> expression only once (so only ordering or only filtering)
>
> Wout.
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
