Order sql query results by the first not null column-CodePudding

I want to order the results of my query by one column, or in case it's null, by a second one.

In MSSQL I usually solve this problem by using the COEALSCE function, so, I write ORDER BY COALESCE(Col1,Col2).

But, although COALESCE function also exists in SqlLite it seems I can't call it from the 'order by' part of my query

Do you have any idea about how I can sort my results?

FIX

COALESCE(Col1,Col2) works with 'simple' queries, What I really want is order the results from a UNION query.

CREATE TABLE FOO
(
    int ID,
    Date1 text,
    Date2 text
)
insert into foo values(1,null,'2021-11-12T009:30:00');
insert into foo values(2,'2021-11-12T008:30:00',null);

-- THIS WORKS!
select * from FOO order by coalesce(Date1,Date2)
 
-- THIS FAILS :(
select * from foo where Date1 = '2021-11-12T008:30:00'
union all
select * from foo where Date2 = '2021-11-12T009:30:00'
order by coalesce(Date1,Date2)

CodePudding user response：

Include the COALESCE in the SELECT clause and order by that. I.E.:

select COALESCE(Date1,Date2) oby,* from foo where Date1 = '2021-11-12T008:30:00'
union all
select COALESCE(Date1,Date2),* from foo where Date2 = '2021-11-12T009:30:00'
order by oby

CodePudding user response：

Put the UNION in a derived table.

SELECT *
       FROM (SELECT *
                    FROM foo
                    WHERE date1 = '2021-11-12T008:30:00'
             UNION ALL
             SELECT *
                    FROM foo
                    WHERE date2 = '2021-11-12T009:30:00') x
       ORDER BY coalesce(date1,
                         date2);