CODEWITHSUNDEEP
sqlpostgresql
piphonom
piphonom

Reputation: 743

UNION queries with desired order

I'm using Postgres and suppose I have a simple table like this

cities
------------------
id | name
------------------
 1 | San Diego
------------------
 2 | San Antonio
------------------
 3 | Stockholm
------------------
 4 | Helsinki
------------------
 5 | San Francisco
------------------

I would like to find all cities that exactly or partially matched to the pattern. And it is important that exactly matched city should be the first in the result. For example if I find city for "San Francisco" the result should be like this

 -------------------
 id | name
 -------------------
  5 | San Francisco
 -------------------
  1 | San Diego
 -------------------
  2 | San Antonio
 -------------------

It is important that "San Francisco" is the first in the result.

My query looks like this

 SELECT * FROM cities WHERE name = "San Francisco"
 UNION
 SELECT * FROM cities WHERE name ~* "San|Francisco"

But this query doesn't guarantee desired order. I tryied to use Partitions to add row_number() and order by it.

WITH cities_with_rows_number AS (
   SELECT *, 0 as row_number FROM cities WHERE name = "San Francisco"
   UNION
   SELECT *, row_number() OVER() as row_number FROM cities WHERE name ~* "San|Francisco"
) SELECT * FROM cities_with_rows_number ORDER BY row_number

But in this case I can't easy eleminate duplicate cities and "San Francisco" will be printed twice. I belive it is possible to write quite simple query to resolve this task.

Upvotes: 0

Views: 28

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

You can use order by:

SELECT *
FROM cities
WHERE name ~* 'San|Francisco'
ORDER BY (name = 'San Francisco')::int DESC;

UNION/UNION ALL is not needed for this query.

Upvotes: 2

Related Questions