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 <kmedc...@dessus.com> 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 <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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
