Menu
Given the following query:
select * from users
where first_name ilike '%foo%'
OR last_name ilike '%bar%'
OR nickname ilike '%foobar%'
Returns:
first_name| last_name | nickname
----------------------------------------
Foo | ABC | abcd
Foo | DEF | efgh
Foo | BAR | ijkl
AMD | Bar | foobar
Foo | Bar | foobar2
Question:
How to sort most relevant (matched) values first?
I mean by most matched that matches more than one pattern inside Where .. OR
Expected Result:
first_name| last_name | nickname
----------------------------------------
Foo | Bar | foobar2
Foo | BAR | ijkl
AMD | Bar | foobar
Foo | ABC | abcd
Foo | DEF | efgh
888
The ORDER BY clause must come after the WHERE, GROUP BY, and HAVING clause if present in the query. Use ASC or DESC to specify the sorting order after the column name. Use ASC to sort the records in ascending order or use DESC for descending order.
Six Operations to Order: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY.
You can use the WHERE clause with or without the ORDER BY statement. You can filter records by finite values, comparison values or with sub-SELECT statements.
SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]; You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort that column should be in the column-list.
Order it by the number of hits:
... ORDER BY (first_name ILIKE '%foo%')::integer
+ (last_name ILIKE '%bar%')::integer
+ (nickname ILIKE '%foobar%')::integer DESC
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
