Re: [sqlite] Mixed ASC and DESC in single column
Wizardry! This is just what I was trying to conceive of. Thank you very
much indeed.
Best wishes,
Hamish
On Thu, 21 Nov 2019 at 17:17, Keith Medcalf wrote:
>
> 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 baz, 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 On
> >Behalf Of Hamish Allan
> >Sent: Thursday, 21 November, 2019 09:09
> >To: SQLite mailing list
> >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 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mixed ASC and DESC in single column
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 On
>Behalf Of Hamish Allan
>Sent: Thursday, 21 November, 2019 09:09
>To: SQLite mailing list
>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 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
Re: [sqlite] Mixed ASC and DESC in single column
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 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 '-' else '' end ||
case when substr(info,1,4) = 'baz/' then '-' else '' end ||
case when substr(info,1,4) = 'foo/' then '' 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
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 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
Re: [sqlite] Mixed ASC and DESC in single column
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
