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 <wout.mert...@gmail.com> 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users