Re: Union question

On Tue, 15 Jul 2008 09:52:58 -0700 (PDT), "amerar_at_iwc.net" <amerar_at_iwc.net> wrote:

>
>Hi,
>
>We have this HUGE query. It is dynamically built, so it will be
>different each time. The problem is, we need to order each query, not
>the entire UNION. So, each select needs to have it's own order by.
>
>Can this be done in any way??
>
>SELECT t_id, body_id, data_w, headline, teaser, name FROM zc_body zc,
>zc_autor za WHERE zc.a_id = za.id AND data_p < SYSDATE AND t_id = 43
>AND rownum <= 1
>UNION
>SELECT t_id, body_id, data_w, headline, teaser, name FROM zc_body zc,
>zc_autor za WHERE zc.a_id = za.id AND data_p < SYSDATE AND t_id = 44
>AND rownum <= 1
>UNION
>SELECT t_id, body_id, data_w, headline, teaser, name FROM zc_body zc,
>zc_autor za WHERE zc.a_id = za.id AND data_p < SYSDATE AND t_id = 45
>AND rownum <= 1
>UNION
>SELECT t_id, body_id, data_w, headline, teaser, name FROM zc_body zc,
>zc_autor za WHERE zc.a_id = za.id AND data_p < SYSDATE AND t_id = 6
>AND rownum <= 1
>UNION
>SELECT t_id, body_id, data_w, headline, teaser, name FROM zc_body zc,
>zc_autor za WHERE zc.a_id = za.id AND data_p < SYSDATE AND t_id = 7
>AND rownum <= 1
>UNION
>SELECT t_id, body_id, data_w, headline, teaser, name FROM zc_body zc,
>zc_autor za WHERE zc.a_id = za.id AND data_p < SYSDATE AND t_id = 8
>AND rownum <= 1
>UNION
>SELECT t_id, body_id, data_w, headline, teaser, name FROM zc_body zc,
>zc_autor za WHERE zc.a_id = za.id AND data_p < SYSDATE AND t_id = 21
>AND rownum <= 1
>UNION
>SELECT t_id, body_id, data_w, headline, teaser, name FROM zc_body zc,
>zc_autor za WHERE zc.a_id = za.id AND data_p < SYSDATE AND t_id = 2
>AND rownum <= 1
>UNION
>SELECT t_id, body_id, data_w, headline, teaser, name FROM zc_body zc,
>zc_autor za WHERE zc.a_id = za.id AND data_p < SYSDATE AND t_id = 35
>AND rownum <= 1
>UNION
>SELECT t_id, body_id, data_w, headline, teaser, name FROM zc_body zc,
>zc_autor za WHERE zc.a_id = za.id AND data_p < SYSDATE AND t_id = 34
>AND rownum <= 1
>ORDER BY data_w DESC;
>
>Thanks all!!!

The problem is, we need to order each query, not
>the entire UNION.

WHY on earth? the UNION will order the data anyway

 So, each select needs to have it's own order by.

Not really.
The query seems to tell you don't know views, you don't know the WITH statement (after posting here for several years you STILL don't include a version number!!!), you don't know the IN clause, you don't know analytic functions etc, etc, etc
The only thing you do know is how to create non-scalable queries. As has been explained many times before here, the rownum pseudo function will be applied AFTER retrieving the result set. You could do this by dumping this mess and replace it by one (1) select with an in-list,partitioning the data by t_id.

-- 
Sybrand Bakker
Senior Oracle DBA