469,089 Members | 1,227 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,089 developers. It's quick & easy.

Ordering by IN

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

Nov 23 '05 #1
3 1283
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

Nov 23 '05 #2
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

Nov 23 '05 #3
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)

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by masood.iqbal | last post: by
2 posts views Thread by D. Dante Lorenso | last post: by
2 posts views Thread by Ken Durden | last post: by
1 post views Thread by Matt Roberts | last post: by
20 posts views Thread by Brian Tkatch | last post: by
33 posts views Thread by Benjamin M. Stocks | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.