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
