Macbernie Macbernie - 3 months ago 19
SQL Question

Search text get "begins by" result first

My SQL request return text fields that begins by the reserch text:

SELECT field1, field2 FROM mytable
WHERE field1 ILIKE 'ex%' OR UNACCENT(field2) ILIKE 'ex%'


And another request do the same, but without necessarily begins by the reserch text:

SELECT field1, field2 FROM mytable
WHERE field1 ILIKE '%ex%' OR UNACCENT(field2) ILIKE '%ex%'


I'm looking for a way to return the results of the first request in first, and the results of the second request following, and if possible without duplicates results because the second request return also the results of the first request...

Or maybe we can just order "begins by" in first, and following the rest (not begins by) ?

Thanks for help

Answer

Simple have one query where you do like '%ex%', to return all rows. Then add an ORDER BY that returns the like 'ex%' rows first.

SELECT field1, field2 FROM mytable
WHERE field1 ILIKE '%ex%' OR UNACCENT(field2) ILIKE '%ex%'
ORDER BY field1 ILIKE 'ex%' OR UNACCENT(field2) ILIKE 'ex%' DESC
Comments