1

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...

François Constant
  • 5,531
  • 1
  • 33
  • 39
  • @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 Jun 17 '20 at 05:18

1 Answers1

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.

jjanes
  • 37,812
  • 5
  • 27
  • 34