How to sort combination of integer and text in PostgreSQL?

Question

I have this table

id  value 
1   OK
2   xminimum
3   NO
4   YES

I want to sort this table by value where minimum is always first then the rest according to alphabetic order of value column

Meaning:

xminimum
NO
OK
YES

I wrote this query:

Select *
from table_a
order by case when value='xminimum' then 1 else ????? end

I don't know what to put in the else... conceptually it should be else value end so it means alphabetic order.. but I can not combine integer with text.

How do I fix it?

Have you tried else 2, or else 0? Then add another item , value to the ORDER BY. — jarlh
– jarlh, Commented Jan 11, 2017 at 9:49
Didn't think of that. It works.. excellent :) post it as reply so I can accept — avi
– avi, Commented Jan 11, 2017 at 9:51

jarlh · Accepted Answer · 2017-01-11 09:58:05Z

4

As requested, copied from my comment:

Select *
from table_a
order by case when value='xminimum' then 1 else 2 end, value

answered Jan 11, 2017 at 9:58

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

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

Comments

Fabian Pijcke · Accepted Answer · 2017-01-11 10:07:58Z

2

Another solution:

SELECT *
FROM table_a
ORDER BY value <> 'xminimum', value;

edited Jan 11, 2017 at 10:07

answered Jan 11, 2017 at 9:54

Fabian Pijcke

3,31027 silver badges30 bronze badges

4 Comments

jarlh Over a year ago

What happens if a value is NULL?

Fabian Pijcke Over a year ago

NULL <> x is always NULL, as by default the NULLs are first my solution was wrong, good point :-) I update it.

Fabian Pijcke Over a year ago

I just checked, NULLS LAST is the default in ascending order, so my solution was correct in the end :D And the answer to your question is: "It will be put as the last result." :-)

jarlh Over a year ago

Thanks for clearing that out! (Some dbms products may have NULLS FIRST as default.)

Frank Schmitt · Accepted Answer · 2017-01-11 09:57:09Z

1

Do it like you have and add the value column as second column to sort by:

SELECT *
FROM table_a
ORDER BY CASE WHEN value='xminimum' THEN 1 ELSE 2 END, value

edited Jan 11, 2017 at 9:57

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

answered Jan 11, 2017 at 9:53

chresse

5,8873 gold badges35 silver badges50 bronze badges

Collectives™ on Stack Overflow

How to sort combination of integer and text in PostgreSQL?

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related