Thread: 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. ---------------------------------------------------------------