'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