|
|
|
|
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
|
|
|
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
|
|
|
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
|
|
|
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-usersA 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
|
|
|
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
|
|
|
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
|
|
Locked
