Re: select where in and order

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.