'Get first unique entry - table with two uuid fields

We want to get unique entries of a table with duplicates.

Simplified we have the following table setup:

id(uuid)    | title (character) | fingerprint (uuid)| created_at(timestamp)
123-0-1-321 | Dogs              | 123-0-1-321       | 2021-12-06 00:00:01
344-0-1-321 | Dog               | 123-0-1-321       | 2018-13-06 00:00:01
888-0-1-321 | Cats              | 888-0-1-321       | 2020-12-06 00:00:01
777-0-1-321 | Birds             | 999-0-1-321       | 2019-12-06 00:00:01
555-0-1-321 | Birds1            | 999-0-1-321       | 2022-12-06 00:00:01

We want to get distinct values by fingerprint. We currently do this by DISTINCT ON which is working fine:

SELECT DISTINCT ON (fingerprint) * 
FROM "tile" 
ORDER BY fingerprint, updated_at DESC;

But now we face a problem with searching by "title" (with an search vector). Problem is that we want to sort the results by search relevance. But because of the DISTINCT ON fingerprint we always have to use fingerprint as first ORDER BY parameter. So the search relevance as 2nd order-by parameter doesn't really work anymore.

I would prefer something like this, but it's not working:

SELECT FIRST(id), fingerprint 
FROM "tile" 
GROUP BY fingerprint 
ORDER BY search_relevance;

MIN(id) and MAX(id) is of course not working too, as id is an uuid-field.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source