SEO

#1 (**permalink**) 04-09-2008, 08:42 AM

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?

Thanks,

qq

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

#2 (**permalink**) 04-09-2008, 08:42 AM

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/

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

#3 (**permalink**) 04-09-2008, 08:42 AM

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.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

select where in and order