SQLite Forum

Using Case in Order By with Union All
Login

Using Case in Order By with Union All

(1) By Gary (1codedebugger) on 2021-02-23 00:00:52 [link] [source]

Would you please tell me what I'm doing wrong or need to do in this query to get it to sort by fullkey as in the case statement?

The error is that the order by term isn't in the result set, but it is there by alias name. If use only order by ro,fk then it works but, of course, $.r is sorted last instead of first.

It appears that, although field fk is in the result set, the result of the case is unrecognized.

The objective is to get the array length from the first select followed by $.r, $.c, and then the individual elements of the array.

Thus, rows: 5, D, E, {"name_1","t":"d"}, ... {"name_5","t":"f"}.

Thank you.

select json_array_length( value ) as v, 1 as ro, fullkey as fk from json_each( '{"r":"D","c":"E","d":[ { "n": "name_1", "t": "d" }, { "n": "name_2", "t": "d" }, { "n": "name_3", "t": "f" },{ "n": "name_4", "t": "f" },{ "n": "name_5", "t": "f" }]}' ) where fullkey = '$.d'

union all

select value as v, 2 as ro, fullkey as fk from json_tree( '{"r":"D","c":"E","d":[ { "n": "name_1", "t": "d" }, { "n": "name_2", "t": "d" }, { "n": "name_3", "t": "f" },{ "n": "name_4", "t": "f" },{ "n": "name_5", "t": "f" }]}' ) where fullkey in ( '$.r', '$.c' ) or path = '$.d' order by ro, case fk when '$.r' then 1 when '$.c' then 2 else 3 end;

(2) By Larry Brasfield (larrybr) on 2021-02-23 00:06:03 in reply to 1 [link] [source]

... order by ro, case fk when '$.r' then 1 when '$.c' then 2 else 3 end;

To me, your ordering clause contains only constants. (String literals here)

I think you need to extract something from the result row(s) to have any effect on the sort.

(3) By Gary (1codedebugger) on 2021-02-23 00:28:40 in reply to 2 [link] [source]

Thank you.

Do you mean because of the UNION ALL or just in general? I ask because it works in the second SELECT alone; it appears to anyway because the order is correct.

(4.2) By Larry Brasfield (larrybr) on 2021-02-23 00:37:29 edited from 4.1 in reply to 3 [link] [source]

I have misread your code.

(5) By Keith Medcalf (kmedcalf) on 2021-02-23 01:28:03 in reply to 1 [source]

I get the following error message:

sqlite> select json_array_length( value ) as v, 1 as ro, fullkey as fk
   ...> from json_each( '{"r":"D","c":"E","d":[ { "n": "name_1", "t": "d" }, { "n": "name_2", "t": "d" }, { "n": "name_3", "t": "f" },{ "n": "name_4", "t": "f" },{ "n": "name_5", "t": "f" }]}' )
   ...> where fullkey = '$.d'
   ...>
   ...> union all
   ...>
   ...> select value as v, 2 as ro, fullkey as fk
   ...> from json_tree( '{"r":"D","c":"E","d":[ { "n": "name_1", "t": "d" }, { "n": "name_2", "t": "d" }, { "n": "name_3", "t": "f" },{ "n": "name_4", "t": "f" },{ "n": "name_5", "t": "f" }]}' )
   ...> where fullkey in ( '$.r', '$.c' ) or path = '$.d'
   ...> order by ro, case fk when '$.r' then 1
   ...>                      when '$.c' then 2
   ...>                      else 3 end;
Error: 2nd ORDER BY term does not match any column in the result set
sqlite>

because you cannot sort a compound select by a non-output column. You need to compute your ordering as part of the component selects.

select json_array_length( value ) as v, 1 as ro, fullkey as fk, case fullkey when '$.r' then 1 when '$.c' then 2 else 3 end as ordering
from json_each( '{"r":"D","c":"E","d":[ { "n": "name_1", "t": "d" }, { "n": "name_2", "t": "d" }, { "n": "name_3", "t": "f" },{ "n": "name_4", "t": "f" },{ "n": "name_5", "t": "f" }]}' )
where fullkey = '$.d'

union all

select value as v, 2 as ro, fullkey as fk, case fullkey when '$.r' then 1 when '$.c' then 2 else 3 end as ordering
from json_tree( '{"r":"D","c":"E","d":[ { "n": "name_1", "t": "d" }, { "n": "name_2", "t": "d" }, { "n": "name_3", "t": "f" },{ "n": "name_4", "t": "f" },{ "n": "name_5", "t": "f" }]}' )
where fullkey in ( '$.r', '$.c' ) or path = '$.d'
order by ro, ordering;

Or perhaps as an ordering of a subselect:

select *
  from (
           select json_array_length( value ) as v, 1 as ro, fullkey as fk
             from json_each( '{"r":"D","c":"E","d":[ { "n": "name_1", "t": "d" }, { "n": "name_2", "t": "d" }, { "n": "name_3", "t": "f" },{ "n": "name_4", "t": "f" },{ "n": "name_5", "t": "f" }]}' )
            where fullkey = '$.d'
        union all
           select value as v, 2 as ro, fullkey as fk
             from json_tree( '{"r":"D","c":"E","d":[ { "n": "name_1", "t": "d" }, { "n": "name_2", "t": "d" }, { "n": "name_3", "t": "f" },{ "n": "name_4", "t": "f" },{ "n": "name_5", "t": "f" }]}' )
            where fullkey in ( '$.r', '$.c' ) or path = '$.d'
       )
order by ro, case fk when '$.r' then 1 when '$.c' then 2 else 3 end;

(6.1) By Keith Medcalf (kmedcalf) on 2021-02-23 01:47:53 edited from 6.0 in reply to 5 [link] [source]

Or perhaps as a CTE (which is just an alternate spelling for the same thing):

with json(json)
  as (
      values ('{"r":"D","c":"E","d":[ { "n": "name_1", "t": "d" }, { "n": "name_2", "t": "d" }, { "n": "name_3", "t": "f" },{ "n": "name_4", "t": "f" },{ "n": "name_5", "t": "f" }]}')
     ),
     data(v, ro, fk)
  as (
         select json_array_length( value ) as v, 1 as ro, fullkey as fk
           from json_each((select json from json))
          where fullkey = '$.d'
      union all
         select value as v, 2 as ro, fullkey as fk
           from json_tree((select json from json))
          where fullkey in ( '$.r', '$.c' ) or path = '$.d'
     )
  select v, ro, fk
    from data
order by ro, case fk when '$.r' then 1 when '$.c' then 2 else 3 end;

or somewhat more succinctly as

with json(json)
  as (
      values ('{"r":"D","c":"E","d":[ { "n": "name_1", "t": "d" }, { "n": "name_2", "t": "d" }, { "n": "name_3", "t": "f" },{ "n": "name_4", "t": "f" },{ "n": "name_5", "t": "f" }]}')
     ),
     data(v, ro, fk)
  as (
         select json_array_length(value), 1, fullkey
           from json, json_each(json.json)
          where fullkey = '$.d'
      union all
         select value, 2, fullkey
           from json, json_tree(json.json)
          where fullkey in ( '$.r', '$.c' ) or path = '$.d'
     )
  select v, ro, fk
    from data
order by ro, case fk when '$.r' then 1 when '$.c' then 2 else 3 end;

(7.1) By Gary (1codedebugger) on 2021-02-23 02:27:40 edited from 7.0 in reply to 5 [link] [source]

Muchas gracias, Senor, for all the examples and explanation.

I had read about the requirement that ORDER BY can be performed only on a column in the combined result, but didn't understand why the fullkey column fk didn't satisfy that. I take it now that it's not only that the ORDER BY must be based on an output column but it must be the value of the column as is without any transformations/translations or whatever the proper term may be for using it in a CASE statement.

I found some examples elsewhere that had the CASE in the outer ORDER BY but didn't pay attention to what SQL database tool they were using and have nothing else installed to test it.

EDIT

If it is of any interest, the code example claimed to work is.

SELECT "Field1" AS field_1, "Field2" AS field_2, "Field3" AS field_3, "Field4" AS field_4 FROM "TableName" WHERE condition AND other_condition UNION ALL SELECT "Field1" AS field_1, "Field2" AS field_2, "Field3" AS field_3, "Field4" AS field_4 FROM "TableName" WHERE yet_another_condition AND yet_another_other_condition ORDER BY CASE field_1 WHEN 'A' THEN 1 WHEN 'B' THEN 2 WHEN 'C' THEN 3 ELSE 4 END

It is from a nearly ten-year-old SO question and I notice now that it concerns PostgreSQL.

I do not know if it truly works because I've never used PostgreSQL nor am I implying that, if it does, SQLite should do the same.

(8) By Keith Medcalf (kmedcalf) on 2021-02-23 03:34:50 in reply to 7.1 [link] [source]

The reason is that the UNION [ALL] is implemented by adding the "rows" from each of the subselects into a temporary table. In the case of a distinct UNION the elimination of duplicate rows is done by accumulating the result set in a temporary b-tree where the entire result row is the key. ORDER BY merely changes the sort order of the b-tree for the accumulation and therefore the result set ordering can be based only on the fields located in the result set.

It would be possible to "sort" the temporary table after the fact (which is what the subselect does). SQLite3 does not implement this directly, however.

It should also be noted that in the original SQL70 the ORDER BY (and group by and having clauses) could only be applied to values contained in the projection result itself. Allowing the use of non-projection-output values in the ORDER BY, GROUP BY and HAVING clauses (to allow direct control of the traversal order) is a new-fangled advancement.