From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
To: | Mage <mage(at)mage(dot)hu> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: subquery join order by |
Date: | 2010-11-19 02:21:50 |
Message-ID: | AANLkTinni1ghy1fCLZMt+VV5pF+ac0SF2f6EMsnd08_O@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 19 November 2010 01:36, Mage <mage(at)mage(dot)hu> wrote:
> Hello,
>
> (I googled and read docs before sending this e-mail).
>
> Is it necessary to use order by twice (inside and outside) to get the proper
> order if I have an ordered subqery in a join?
>
> select * from (select distinct on (b_id) * from a order by b_id, id) sub
> left join b on b.id = sub.b_id;
>
> or
>
> select * from (select distinct on (b_id) * from a order by b_id, id) sub
> left join b on b.id = sub.b_id order by b_id;
>
>
> It seems to me that it's enough to use 'order by' only inside wheter 'by
> desc' or 'by asc' (b_id), however I'd like to be sure.
>
> Thank you.
>
> Mage
You should always use ORDER BY on the outer-most part of the query
since that's what will be finally returning your data. Don't bother
with ordering sub-selects.
So in your case, just use:
SELECT *
FROM (SELECT DISTINCT ON (b_id) * FROM a) sub
LEFT JOIN b ON b.id = sub.b_id
ORDER BY sub.b_id, sub.id;
But why bother with a sub-select anyway? You can write it as:
SELECT DISTINCT ON (a.b_id) *
FROM a
LEFT JOIN b ON b.id = a.b_id
ORDER BY a.b_id, a.id;
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
From | Date | Subject | |
---|---|---|---|
Next Message | Jayadevan M | 2010-11-19 03:23:26 | Re: Survey on backing up unlogged tables: help us with PostgreSQL development! |
Previous Message | Mage | 2010-11-19 01:36:39 | subquery join order by |