Re: ORDER BY a previous result
From: Carlos <miotromailcarlos_at_netscape.net>
Date: Tue, 17 Feb 2009 03:03:21 -0800 (PST)
Message-ID: <9eaa993e-edf3-472e-a9e6-bde423dff039_at_z1g2000yqn.googlegroups.com>
On 17 feb, 11:31, "Álvaro G. Vicario"
<alvaro.NOSPAMTH..._at_demogracia.com> wrote:
> Is there a simple way to use a previous result in an ORDER BY clause?
>
> I have a rather complicate query that filters, sorts and returns a
> series of IDs:
>
> FOO_ID
> ======
> 1
> 98
> 12
> 33
>
> Then, I use these IDs to fetch further information about the items they
> represent:
>
> SELECT .......
> FROM FOO
> LEFT JOIN BAR .......
> WHERE FOO_ID IN (1, 98, 12, 33)
>
> I keep the two queries separate to avoid excessive complexity. I compose
> the SQL code using PHP.
>
> Right now, the second query comes unsorted from Oracle: I use PHP to
> sort it at a later stage in my application (my PHP skills are better
> than my SQL ones). Would it be possible to use the ID list to sort the
> second query inside Oracle?
>
> Server runs Oracle9i Enterprise Edition Release 9.2.0.1.0
>
> Thank you in advance.
>
> --
> --http://alvaro.es- Álvaro G. Vicario - Burgos, Spain
> -- Mi sitio sobre programación web:http://bits.demogracia.com
> -- Mi web de humor al baño María:http://www.demogracia.com
> --
Date: Tue, 17 Feb 2009 03:03:21 -0800 (PST)
Message-ID: <9eaa993e-edf3-472e-a9e6-bde423dff039_at_z1g2000yqn.googlegroups.com>
On 17 feb, 11:31, "Álvaro G. Vicario"
<alvaro.NOSPAMTH..._at_demogracia.com> wrote:
> Is there a simple way to use a previous result in an ORDER BY clause?
>
> I have a rather complicate query that filters, sorts and returns a
> series of IDs:
>
> FOO_ID
> ======
> 1
> 98
> 12
> 33
>
> Then, I use these IDs to fetch further information about the items they
> represent:
>
> SELECT .......
> FROM FOO
> LEFT JOIN BAR .......
> WHERE FOO_ID IN (1, 98, 12, 33)
>
> I keep the two queries separate to avoid excessive complexity. I compose
> the SQL code using PHP.
>
> Right now, the second query comes unsorted from Oracle: I use PHP to
> sort it at a later stage in my application (my PHP skills are better
> than my SQL ones). Would it be possible to use the ID list to sort the
> second query inside Oracle?
>
> Server runs Oracle9i Enterprise Edition Release 9.2.0.1.0
>
> Thank you in advance.
>
> --
> --http://alvaro.es- Álvaro G. Vicario - Burgos, Spain
> -- Mi sitio sobre programación web:http://bits.demogracia.com
> -- Mi web de humor al baño María:http://www.demogracia.com
> --
>Server runs Oracle9i Enterprise Edition Release 9.2.0.1.0
Dessuported-unpatched DB version.
> I keep the two queries separate to avoid excessive complexity. I compose
> the SQL code using PHP.
Wrong design: better one query that does all the work.
>Would it be possible to use the ID list to sort the
> second query inside Oracle?
Yes. You should extract the ID and the order you want and re-write the second query to use an ORDER BY clause on the 'order' column.
SELECT .......
FROM FOO a
LEFT JOIN BAR b.......
ON a.FOO_ID = b.FOO_ID
ORDER BY b.MY_ORDER
Cheers.
Carlos. Received on Tue Feb 17 2009 - 05:03:21 CST