On 19 November 2010 01:36, Mage <mage@xxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
