On 11/21/2019 8:08 AM, Hamish Allan wrote: > Thank you very much Clemens, but I've realised I've asked the wrong > question. > > Is it possible to achieve the same if the identifiers are not unique? > > So for instance: > > CREATE TABLE IF NOT EXISTS Data (identifier TEXT, info TEXT); > > INSERT INTO Data VALUES ("id1", "foo/2"); > INSERT INTO Data VALUES ("id1", "bar/3"); > INSERT INTO Data VALUES ("id1", "baz/1"); > > INSERT INTO Data VALUES ("id2", "foo/1"); > INSERT INTO Data VALUES ("id2", "bar/2"); > INSERT INTO Data VALUES ("id2", "baz/2"); > > INSERT INTO Data VALUES ("id3", "foo/1"); > INSERT INTO Data VALUES ("id3", "bar/2"); > INSERT INTO Data VALUES ("id3", "baz/1"); > > If I want the query to be like "bar ASC, foo DESC, baz ASC", it should > return: > > id3 -- (bar/2, foo/1, baz/1) > id2 -- (bar/2, foo/1, baz/2) > id1 -- (bar/3) > > Or if I want a query like "baz DESC, foo DESC, bar ASC": > > id2 -- (baz/2) > id1 -- (baz/1, foo/2) > id3 -- (baz/1, foo/1) > > I tried: > > SELECT * FROM (SELECT identifier FROM Data WHERE info LIKE 'bar/%' ORDER BY > info ASC) > UNION > SELECT * FROM (SELECT identifier FROM Data WHERE info LIKE 'foo/%' ORDER BY > info DESC) > UNION > SELECT * FROM (SELECT identifier FROM Data WHERE info LIKE 'baz/%' ORDER BY > info ASC); > > but of course this approach no longer works... > > Thanks again, > Hamish > > > > > On Thu, 21 Nov 2019 at 14:02, Clemens Ladisch <clem...@ladisch.de> wrote: > >> Hamish Allan wrote: >>> I want to get the uuids in order as if `foo`, `bar` and `bar` were >>> different columns, e.g. if the desired order were "bar ASC, foo DESC, baz >>> ASC" >> SELECT * FROM (SELECT uuid FROM Data WHERE info LIKE 'bar/%' ORDER BY info >> ASC) >> UNION ALL >> SELECT * FROM (SELECT uuid FROM Data WHERE info LIKE 'foo/%' ORDER BY info >> DESC) >> UNION ALL >> SELECT * FROM (SELECT uuid FROM Data WHERE info LIKE 'baz/%' ORDER BY info >> ASC); >> >> >> Regards, >> Clemens >> _______________________________________________ >> 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
A possible solution would be to create a computed field for sorting, flexible but wordy. Something like: Select * from (Select Data.* case when substr(info,1,4) = 'bar/' then '<sort prefix>-' else '' end || case when substr(info,1,4) = 'baz/' then '<sort prefix>-' else '' end || case when substr(info,1,4) = 'foo/' then '<sort prefix>' else '' end as sortfield from Data ) order by sortfield,... ) The sort might also be another joined table _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users