Re: Union question

On Jul 15, 12:06 pm, sybra..._at_hccnet.nl wrote:
> On Tue, 15 Jul 2008 09:52:58 -0700 (PDT), "ame..._at_iwc.net"
>
>
>
>
>
> <ame..._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- Hide quoted text -
>
> - Show quoted text -

Listen butthead jackass, we're all glad that you are the #1 DBA on the planet earth if not in the whole galaxy. We've all seen your rude and garbage attitude. So, if you cannot lend any meaningful help, so visit your inlaws or something...... Received on Tue Jul 15 2008 - 12:09:04 CDT