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?

Mike Organek · Accepted Answer · 2020-07-02 17:11:35Z

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 2, 2020 at 17:11

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

StOMicha Over a year ago

Thank you, perfect!!

Tomáš Záluský · Accepted Answer · 2020-07-02 17:27:19Z

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 2, 2020 at 17:27

Tomáš Záluský

12.3k4 gold badges44 silver badges74 bronze badges

1 Comment

StOMicha Over a year ago

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.

Collectives™ on Stack Overflow

PostgreSQ: JOIN SELECT and ORDER hidden

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related