Sort results of a query by the order of OR clause

Question

I have a string as input, that represents an hostname, and a table that contains suffixes (e.g. co.uk, uk, etc).

I'd like the database to return the records based on the order of the where clause. For example, let's assume the suffixes table contains the following entries:

| id | name   |
|----|--------|
| 1  | co.uk  |
| 2  | uk     |
| 3  | foo.uk |

Assuming the input is example.co.uk, I will produce a query like the following one:

SELECT *
FROM suffixes
WHERE
  name = "example.co.uk" OR
  name = "co.uk" OR
  name = "uk"
LIMIT 1

The result should be:

| 1  | co.uk  |

However, from tests I executed, it is not guaranteed that PG follows the order of the OR conditions by defaults. In some cases, when there are multiple entries matching the OR conditions (e.g. in this case co.uk and uk), it may very well return uk.

In fact, if I take the same query and swap the OR conditions:

SELECT *
FROM suffixes
WHERE
  name = "uk" OR
  name = "co.uk"
LIMIT 1

in most cases it returned the same result of the previous query.

How can I tell PostgreSQL to return the first match of the OR query, in the order specified in the query?

Answer 1

There are two ways to do this in Postgres:

Join your table against a virtual table build from a values list:

with names (idx, name) as (
  values 
    (1, 'example.co.uk'), 
    (2, 'co.uk'), 
    (3, 'uk')
)
select s.*
from suffixes s
  join names n on n.name = s.name
order by n.idx
limit 1;

Another shorter version would be to join against an array:

select s.*
from suffixes s
  join unnest(array['example.co.uk', 'uk', 'co.uk']) with ordinality as n(name, idx) 
    on n.name = s.name
order by n.idx
limit 1

The second version has the advantage, that you don't need to supply the additional index because that's implicitly defined through the order of the values inside the array.

Answer 2

Ordering is only guaranteed with an ORDER BY clause. So you will have to do some work in an ORDER BY here if your sorting isn't alphabetical/numeric.

SELECT *
FROM suffixes
WHERE
  name = 'example.co.uk' OR
  name = 'co.uk' OR
  name = 'uk'
order by 
case 
when name = 'example.co.uk' then 1
when name = 'co.uk' then 2
when name = 'uk' then 3
else 4
end 
LIMIT 1;

If your WHERE clause changes, you'll have to make the corresponding change to your ORDER BY.

It would most likely be more efficient (for both execution and development) to have a lookup table which orders your TLDs, then join to that table on the TLD and order by the sort order column from that second table.

Answer 3

For your exact example I would suggest ordering by the string length:

SELECT *
FROM suffixes
WHERE
  name = "example.co.uk" OR
  name = "co.uk" OR
  name = "uk"
ORDER BY
  char_length(name) desc

---

Another thing which could work is using UNION ALL:

I chose UNION ALL over regular UNION because we don't have to worry about duplicate records between the unions since the WHERE clause is using an exact comparison

  SELECT *, 1 as synthetic_sort_order
  FROM suffixes
  WHERE
    name = "example.co.uk"
UNION ALL
  SELECT *, 2
  FROM suffixes
  WHERE
    name = "co.uk"
UNION ALL
  SELECT *, 3
  FROM suffixes
  WHERE
    name = "uk"
ORDER BY synthetic_sort_order asc

According to https://stackoverflow.com/q/22395883 there is no limit on UNION clauses except that if it gets too large then your performance could suffer.