Re: [sqlite] Mixed ASC and DESC in single column

[Jim Morris]

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