PostgreSQL - sort by array_position and distinct on another column

Question

I have a query which looks like this (actual query & tables are more complicated):

SELECT item.other_uuid, item.uuid, category.name
FROM item, category
WHERE ...
ORDER BY array_position(ARRAY[{'CDX', 'KDJ', 'PLM', 'OLA', 'OWK', 'CAT'}]::uuid[], item.uuid), category.name;

That gives me a list of category names in a particular order (uuid order passed by the array). For example:

789 CDX "Cat D"
123 KDJ "Cat A"
456 PLM "Cat B"
123 OLA "Cat F"
456 OWK "Cat X"
123 CAT "Cat Z"

What I'd like to do is add a DISTINCT ON 'item.other_uuid' to that query. So the resulting list would be:

789 CDX "Cat D"
123 KDJ "Cat A"
456 CAT "Cat B"

How can I achieve this? Postgres wants ORDER BY & DISTINCT to match but that wouldn't be the right order for my query.

I've tried to make that a subquery and use DISTINCT in the main query but the order isn't maintained...

@jjanes You are correct, I've made a mistake in my example. I've just updated it. First column is 'other_uuid', the array position was incorrect. — François Constant
– François Constant, Commented Jun 17, 2020 at 5:18

jjanes · Accepted Answer · 2020-06-17 12:57:18Z

2

There are two components here, which member is chosen for each group of "other_uuid", and which order those selected rows are presented in. If you wish to manage both of those things, you might need an inner query and an outer query, each with their own ORDER BY.

select * from (
   SELECT distinct on (other_uuid)other_uuid, uuid, name
   FROM ...
   ORDER BY other_uuid, array_position(ARRAY['CDX', 'KDJ', 'PLM', 'OLA', 'OWK', 'CAT']::text[], uuid), name
) foo order by array_position(ARRAY['CDX', 'KDJ', 'PLM', 'OLA', 'OWK', 'CAT']::text[], uuid);

The repetition of the array_position expression is ugly. There are ways to avoid it, but those are also ugly.

answered Jun 17, 2020 at 12:57

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL - sort by array_position and distinct on another column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related