'postgresql order by similarity (trigrams) on 2 fields
I used trigrams on 2 fields in my table and want to sort by MAX similarity comparing both fields during the request.(Postgresql 11.x)
I have difficulties to do this:
- I can't use aliases in order by
- If I replace full code by similarity(...) I have this error:
Query 1 ERROR: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
My current request with aliases not working in ORDER BY of godd is:
SELECT DISTINCT
similarity (msr_references.searchable, 'my simple test') AS similarity,
similarity (msr_references.simplified_searchable, 'my simple test') AS similarity_simplified,
msr_references.name, msr_references.searchable, msr_references.simplified_name, msr_references.simplified_searchable
FROM
"msr_references"
WHERE
similarity(msr_references.searchable, 'my simple test') >= 0.3
OR
similarity(msr_references.simplified_searchable, 'my simple test') >= 0.3
ORDER BY
CASE WHEN similarity >= similarity_simplified THEN similarity END DESC,
CASE WHEN similarity_simplified >= similarity THEN similarity_simplified END DESC
LIMIT 10;
The goal of the request is to sort by MAX similarity between the 2 fields, but for all records.
Example of good sorting:
similarity | similarity_simplified
0.3 0.55
0.51 0.2
0.46 0.31
0.32 0.43
0.39 0.36
Is this possible technically ? Thanks,
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
