How to query with a preference with LIKE?

I have this query, using Postgres:

select *
from jobs
where short_name like '%GES.PAY%'
order by short_name;

which returns this result :

CRD.GES.PAY
GES.PAY
REL.GES.PAY

But how to have ‘GES.PAY’ the first line of the result , like that :

GES.PAY
CRD.GES.PAY
REL.GES.PAY

In other words I would like the exact name in first line, then the other cases. Is it possible to do that in one simple query, without using Levenshtein functions?

Answer

You can do conditional ordering:

order by 
   case when short_name = 'GES.PAY' then 0 else 1 end,
   short_name

In Postgres you can also express this as:

order by (short_name = 'GES.PAY')::int desc, short_name

Th latter works because (condition)::int returns 1 if the condition is fulfilled, else 0.

Advertisement

Answer