Mixed ASC and DESC in single column

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Mixed ASC and DESC in single column

metanym
Hi,

Given a table like the following:

CREATE TABLE IF NOT EXISTS Data (uuid TEXT, info TEXT);

with data like:

C94464EB|foo/x1
8A740A4C|foo/x2
FBC49814|bar/x1
F9B0921F|bar/y1
1914F587|bar/y2
E51EC596|baz/a1
549298B6|baz/a2
822DC1A8|foo/x2/a1
46F2854F|foo/x1/a1
47FE9DB1|bar/z1
841716A3|bar/y1/b1
BF965FFA|baz/x1

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" I would want to retrieve the data in this order:

FBC49814|bar/x1
F9B0921F|bar/y1
841716A3|bar/y1/b1
1914F587|bar/y2
47FE9DB1|bar/z1
822DC1A8|foo/x2/a1
8A740A4C|foo/x2
46F2854F|foo/x1/a1
C94464EB|foo/x1
E51EC596|baz/a1
549298B6|baz/a2
BF965FFA|baz/x1

(I only actually need the `uuid`; I'm just repeating the `info` for clarity
here.)

NB the number of sort types like `foo`, `bar` and `baz` can vary. And this
is for usage through the C API, so prepared queries would be ideal, but I
can use custom functions if need be.

I can easily think of how I would approach it if I didn't need the
different types to be able to have different ASC and DESC; but I haven't
been able to devise an general approach.

Can anyone give me any ideas?

Many thanks,
Hamish
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mixed ASC and DESC in single column

Clemens Ladisch
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mixed ASC and DESC in single column

metanym
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mixed ASC and DESC in single column

Keith Medcalf
In reply to this post by metanym

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 <[hidden email]> On
>Behalf Of Hamish Allan
>Sent: Thursday, 21 November, 2019 09:09
>To: SQLite mailing list <[hidden email]>
>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 <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mixed ASC and DESC in single column

metanym
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 <[hidden email]> 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 <[hidden email]> On
> >Behalf Of Hamish Allan
> >Sent: Thursday, 21 November, 2019 09:09
> >To: SQLite mailing list <[hidden email]>
> >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 <[hidden email]> 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
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users