Re: Union question

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 15 Jul 2008 19:58:25 +0200
Message-ID: <487CE541.1050109@gmail.com>

amerar_at_iwc.net schrieb:

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

I can't see any reason to be rude, especially, for correct advice given. Technically, it is possible to order data from different *union all* subsets (by including a auxiliary column in select list) - but it doesn't apply in your case anyway, besides that - what do you expect from ordering of *single row* - should it be the first row, or the last row? I have no idea, how your sql generator works, but Sybrand is absolutely correct, it produces very ugly sql.

Best regards

Maxim Received on Tue Jul 15 2008 - 12:58:25 CDT