with a (identifier, prefix, suffix) as (
select identifier,
substr(info, 1, instr(info, '/') - 1),
substr(info, instr(info, '/') + 1)
from data
where instr(info, '/') > 1
),
b (identifier, bar, foo, baz) as (
select identifier,
max(case prefix when 'bar' then suffix end),
max(case prefix when 'foo' then suffix end),
max(case prefix when 'baz' then suffix end)
from a
group by identifier
)
select * from b order by bar, foo desc, baz;
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Hamish Allan
>Sent: Thursday, 21 November, 2019 09:09
>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>Subject: Re: [sqlite] Mixed ASC and DESC in single column
>
>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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
