Cfrgtkky

I'm using Postgres 9.6 and I have a query that shows the top 100 rows that contain a specific string, "xyz" in this example. It looks roughly like the following:

SELECT name FROM data WHERE name ILIKE '%xyz%' ORDER BY other_column LIMIT 100;

What I actually want to achieve is to put the rows that match at the beginning of the name column in front. So still 100 rows in total, but first filled from results that match name ILIKE 'xyz%, and then with results from ILIKE '%xyz%'.

I tried to achieve this with UNION queries roughly like the following

SELECT name FROM data WHERE name ILIKE 'xyz%' 

UNION

SELECT name FROM data WHERE name ILIKE '%xyz%'

ORDER BY other_column 

LIMIT 100;

This obviously doesn't work because it's sorted by other_column. My original idea was to add a column to each query to use for sorting

SELECT 1 as sort_order, name FROM data WHERE name ILIKE 'xyz%' 

UNION

SELECT 2 as sort_order, name FROM data WHERE name ILIKE '%xyz%'

ORDER BY sort_order, other_column 

LIMIT 100;

But that kills the duplicate removal of UNION and instead behaves like UNION ALL because I'm making originally identical rows different with the addition of the sort_order column.

I could of course do the duplicate removal in a later step outside the database, but that isn't a terribly appealing solution to me. Is there any way to order the individual parts of a UNION query separately, and achieve the results I described?

Use the function position(substring in string) instead of UNION, example:

WITH data(name, other_column) AS (

VALUES

    ('abc xyz', 1),

    ('xyz abc', 2),

    ('a xyz b', 3),

    ('xyz abc', 4)

)



SELECT name, other_column

FROM data 

WHERE name ILIKE '%xyz%'

ORDER BY position('xyz' in name), other_column

LIMIT 100;



  name   | other_column 

---------+--------------

 xyz abc |            2

 xyz abc |            4

 a xyz b |            3

 abc xyz |            1

(4 rows)

or:

SELECT name, other_column

FROM data 

WHERE name ILIKE '%xyz%'

ORDER BY position('xyz' in name) > 1, other_column

LIMIT 100;



  name   | other_column 

---------+--------------

 xyz abc |            2

 xyz abc |            4

 abc xyz |            1

 a xyz b |            3

(4 rows)    

By using other_column:

SELECT t.name FROM (

    SELECT name, CONCAT(' ', name) AS other_column FROM data WHERE (name ILIKE 'xyz%')

    UNION

    SELECT name, name AS other_column FROM data WHERE (name ILIKE '%xyz%') AND (name NOT ILIKE 'xyz%')

) AS t

ORDER BY t.other_column 

LIMIT 100;

What this does is append a prefix of blank space (ASCII 32) to name in the first list and creates other_column.

In the second list other_column is the same as the name.

Finally sorts by other_column.