Ordering by IN

[Hadley Willan]

Hi,
I was wondering if it's possible to order the result set by some of
the set contained in an IN clause.

For example.
SELECT * FROM v_fol_unit_pub_utmpt WHERE folder_folder_object = 100120
AND unit IN ( 90072, 90005, 90074, 90075 ) AND unit_pub_type IN ( 2 )
AND utmpt IN ( 1 );

Results in.

folder_folder_object | unit | unit_type | unit_quantity |
unit_pub_type | utmpt
----------------------+-------+-----------+---------------+---------------+-------
100120 | 90005 | 101 | 1 |
2 | 1
100120 | 90072 | 101 | 1 |
2 | 1
100120 | 90074 | 101 | 1 |
2 | 1

When ideally I'd like to maintain the order as per ordered list of unit
ids passed in as the parameters.
Like so;

folder_folder_object | unit | unit_type | unit_quantity |
unit_pub_type | utmpt
----------------------+-------+-----------+---------------+---------------+-------
100120 | 90072 | 101 | 1 |
2 | 1
100120 | 90005 | 101 | 1 |
2 | 1
100120 | 90074 | 101 | 1 |
2 | 1
Is this even possible?

Thanks

[Eric B.Ridge]

On Aug 25, 2004, at 2:18 AM, Hadley Willan wrote:

Hi,
*** I was wondering if it's possible to order the result set by some
of the set contained in an IN clause.

I had to do something like this recently. Ended up with a pl/pgsql
function, looked a lot like this:

create or replace function array_find (int8, int8[]) returns int4 as
'declare
data alias for $1;
arr alias for $2;
cnt int4;
begin
cnt := 1;
while arr[cnt] is not null loop
if data = arr[cnt] then
return cnt;
end if;
cnt := cnt + 1;
end loop;

return null;
end;'
language 'plpgsql';

Then, using your original query:

SELECT * FROM v_fol_unit_pub_utmpt
WHERE folder_folder_object = 100120 AND
unit IN ( 90072, 90005, 90074, 90075 ) AND
unit_pub_type IN ( 2 ) AND
utmpt IN ( 1 )
ORDER BY array_find(unit, '{90072, 90005, 90074, 90075}');

Notice that the values are repeated in the ORDER BY clause, in the form
of an array. VeryImportant. Also note that I'm assuming the datatype
of "unit" is an int8... you'll want to adjust the arguments of the
function appropriately for the actual datatype.

With my luck, somebody will respond with a "but postgres already has an
array_find-like function"... but if it does, I couldn't find it.

eric
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Mike Benoit]

It might not be pretty but:

SELECT * FROM v_fol_unit_pub_utmpt WHERE folder_folder_object = 100120
AND unit IN ( 90072, 90005, 90074, 90075 ) AND unit_pub_type IN ( 2 )
AND utmpt IN ( 1 ) ORDER BY unit = 90072 desc, unit = 90005 desc, unit =
90074 desc;

It probably won't work very well if you need to specify more then about
10 IDs to order by.
I just discovered Postgres supports this syntax:

ORDER BY unit in ( 90072, 90005, 90074 ) desc

It seems to order the IDs in the reverse order they are listed in the IN
clause. I don't fully understand the behavior of the above case though,
it seems to do weird things with different queries. Give it a shot
though.

On Wed, 2004-08-25 at 18:18 +1200, Hadley Willan wrote:

Hi,
I was wondering if it's possible to order the result set by some
of the set contained in an IN clause.

For example.
SELECT * FROM v_fol_unit_pub_utmpt WHERE folder_folder_object = 100120
AND unit IN ( 90072, 90005, 90074, 90075 ) AND unit_pub_type IN ( 2 )
AND utmpt IN ( 1 );

Results in.

folder_folder_object | unit | unit_type | unit_quantity |
unit_pub_type | utmpt
----------------------+-------+-----------+---------------
+---------------+-------
100120 | 90005 | 101 | 1 |
2 | 1
100120 | 90072 | 101 | 1 |
2 | 1
100120 | 90074 | 101 | 1 |
2 | 1

When ideally I'd like to maintain the order as per ordered list of
unit ids passed in as the parameters.
Like so;

folder_folder_object | unit | unit_type | unit_quantity |
unit_pub_type | utmpt
----------------------+-------+-----------+---------------
+---------------+-------
100120 | 90072 | 101 | 1 |
2 | 1
100120 | 90005 | 101 | 1 |
2 | 1
100120 | 90074 | 101 | 1 |
2 | 1
Is this even possible?

Thanks

--
Mike Benoit <ip**@snappymail.ca>
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Greg Stark]

Mike Benoit <ip**@snappymail.ca> writes:

I just discovered Postgres supports this syntax:

ORDER BY unit in ( 90072, 90005, 90074 ) desc

It seems to order the IDs in the reverse order they are listed in the IN
clause. I don't fully understand the behavior of the above case though,
it seems to do weird things with different queries. Give it a shot
though.

That's just sorting by the boolean value of whether unit is in the set or not.
It's not doing what you want.

You could do something like

SELECT *
FROM a JOIN ( select 90072 as unit
union all select 90005
union all select 90074) as x using (unit)

But even that is NOT going to be guaranteed to work. If it happens to choose a
nested loop from the union against a then I think it would result in the right
order. But if it decides to use a hash join or merge join then it's going to
result in other orderings.

You would have to make that more elaborate and cumbersome with

SELECT *
FROM a JOIN ( select 90072 as unit, 1 as pos
union all select 90005,2
union all select 90074,3
) as x using (unit)
ORDER BY pos
If you load the very useful contrib/intarray module you could use the clean
nice notation:

ORDER BY idx(array[90072,90005,90074], unit)

--
greg
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)