PostgreSQ: JOIN SELECT and ORDER hidden

Question

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?

score 1 · Accepted Answer · answered Jul 02 '20 at 17:11

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;

answered Jul 02 '20 at 17:11

Mike Organek

11,647
3
11
26

1

Thank you, perfect!! – StOMicha Jul 02 '20 at 17:18

score 0 · Answer 2 · answered Jul 02 '20 at 17:27

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.

answered Jul 02 '20 at 17:27

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

PostgreSQ: JOIN SELECT and ORDER hidden

2 Answers2