select where in and order

Tony Smith wrote on
Thursday, March 09, 2006 6:33 PM

To: pgsql-general@postgresql.org
Subject: [GENERAL] select where in and order

I have two tables action and group:

action
------------------------
id,
name

group:
------------------------
action_id
rank

I what to select from action table by order by the
rank in the group table.

If I use

select * from action where id in (select action_id
from group order by rank)

Try something like:

select a.*, g.action_id
FROM action a
INNER JOIN group g
ON a.id = g.action_id
ORDER BY g.action_id

Tony Smith wrote:

I have two tables action and group:

action
------------------------
id,
name

group:
------------------------
action_id
rank

I what to select from action table by order by the
rank in the group table.

If I use

select * from action where id in (select action_id
from group order by rank)

Why not

select * from action a, group g where a.id=g.action_id order by rank desc;

?

--
Postgresql & php tutorials
http://www.designmagick.com/

On Thu, 9 Mar 2006, Tony Smith wrote:

I have two tables action and group:

action
------------------------
id,
name

group:
------------------------
action_id
rank

I what to select from action table by order by the
rank in the group table.

If I use

select * from action where id in (select action_id
from group order by rank)

The action may not be ordered by rank. How can I do
it?

Well, I think any answer is going to depend on a few
pieces of information about the layout and desired behavior.

Is group.action_id unique?
If so, probably converting it into a join is easiest,
I think that'd be something like:
select action.* from action, group
where action.id=group.action_id
order by rank

If not, which rank do you want to use from group for
a matching id? You could probably then do something with
group by and an aggregate.