sql how to sort by a union’s subquery without adding a column?

So I mostly have the query I need, but I noticed that I still get duplicates from the first select and the second select. I thought that using UNION instead of UNION ALL would remove duplicates, but because they have a different sequence number they are not removed. How can I order my results by the select statement without adding an unnecessary seq column?

select 1 as seq, t.* from template t 
WHERE status = 'ACTIVE' and t.title ~* '.*동s*아s*리s*로s*고s*.*' 
UNION 
select 2 as seq, t.* from template t 
WHERE status = 'ACTIVE' and t.title ~* any(array['.*동s*아s*리s*로s*고s*.*']) 
UNION select 3 as seq, t.* from template t WHERE status = 'ACTIVE' 
order by seq asc

Answer

You can do this using order by:

select t.*
from template t 
where status = 'ACTIVE' 
order by (t.title ~* '.*동s*아s*리s*로s*고s*.*') desc,
         (t.title ~* any(array['.*동s*아s*리s*로s*고s*.*']) desc;

Advertisement

Answer