Обсуждение: Order by sub-select

In the following query:
SELECT name, contact, phone, email FROM eforward.address
        WHERE gsb IN ( SELECT gsb FROM eforward.ibox
                WHERE ibx IN ( SELECT DISTINCT ibx FROM eforward.partner
                        WHERE pibx = 'MTCBIN'));

How would I sort the result by eforward.partner.ibx?  I tried an 'ORDER
BY' clause in the sub-select, but that doesn't appear to work.  (If you
drop the outer query, the order does appear to be maintained however.)

I'm using postgres 8.1.0.

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------

On Jan 9, 2008 12:12 PM, Daniel T. Staal <DStaal@usa.net> wrote:
> How would I sort the result by eforward.partner.ibx?

Try (untested):

SELECT DISTINCT name, contact, phone, email
FROM            eforward.address
                JOIN eforward.ibox
                ON eforward.address.gsb = eforward.ibox.gsb
                JOIN eforward.partner
                ON eforward.ibox.ibx = eforward.partner.ibx
WHERE           eforward.partner.pibx = 'MTCBIN'
ORDER BY        eforward.partner.ibx;

On Wed, January 9, 2008 12:39 pm, Rodrigo E. De León Plicet wrote:
> On Jan 9, 2008 12:12 PM, Daniel T. Staal <DStaal@usa.net> wrote:
>> How would I sort the result by eforward.partner.ibx?
>
> Try (untested):
>
> SELECT DISTINCT name, contact, phone, email
> FROM            eforward.address
>                 JOIN eforward.ibox
>                 ON eforward.address.gsb = eforward.ibox.gsb
>                 JOIN eforward.partner
>                 ON eforward.ibox.ibx = eforward.partner.ibx
> WHERE           eforward.partner.pibx = 'MTCBIN'
> ORDER BY        eforward.partner.ibx;

Thanks.  'DISTINCT' gives an error then, but that doesn't happen to be a
problem in my case.  (If it was, I could probably figure out a way to
include ibx in the final output...)

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------