0

A database table "myTable" has the field "category";

SELECT DISTINCT "category" FROM "myTable" ORDER BY "category" ASC;
   A-Class
   T-Class
   Z-Class

OK, now I have to add a line 'undefined', e.g.

SELECT DISTINCT "category" FROM "myTable"  
UNION SELECT '>undefined<' 
ORDER BY "category" ASC;
   A-Class
   T-Class
   >undefined<
   Z-Class

I need the 'undefined' as first entry in the list and tried several characters like .,-_<>! at the first place, but all are ignored and the u is taken for the order.

Is there any simple option to achieve this, without showing a second column in the query?

StOMicha
  • 315
  • 1
  • 3
  • 12

2 Answers2

1

Just use a subquery or a CTE:

with sortme as (
  select distinct category, category as category_sort 
    from "myTable"
  union 
  select 'undefined', ''
)
select category
  from sortme
 order by category_sort;
Mike Organek
  • 11,647
  • 3
  • 11
  • 26
0

You can type

order by case "category" when 'undefined' then 1 else 0 end, "category";

With special characters such as > before undefined, you could also cast category to bytea or use explicit collation.

Tomáš Záluský
  • 10,735
  • 2
  • 36
  • 64
  • Thank you, order by case is a good proposal and shorter.Unfurtunately, in some of my cases the text to be ORDERed is a SubSELECT of another table and I could not get this to work in combination with the UNION. – StOMicha Jul 03 '20 at 07:53