Re: Union question

On Jul 15, 12:58 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> ame..._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- Hide quoted text -
>
> - Show quoted text -

I tried this query, but again, it is ordering by the entire result, I need each select ordered seperately.....

WITH tbl AS (
  SELECT t_id, body_id, data_w, headline, teaser, name, type FROM zc_body zc, zc_autor za, zc_type zt WHERE zc.a_id = za.id AND data_p < SYSDATE AND zt.id = t_id AND t_id = 43 AND rownum <= 1 UNION   SELECT t_id, body_id, data_w, headline, teaser, name, type FROM zc_body zc, zc_autor za, zc_type zt WHERE zc.a_id = za.id AND data_p < SYSDATE AND zt.id = t_id AND t_id = 44 AND rownum <= 4 UNION   SELECT t_id, body_id, data_w, headline, teaser, name, type FROM zc_body zc, zc_autor za, zc_type zt WHERE zc.a_id = za.id AND data_p < SYSDATE AND zt.id = t_id AND t_id = 45 AND rownum <= 3 UNION   SELECT t_id, body_id, data_w, headline, teaser, name, type FROM zc_body zc, zc_autor za, zc_type zt WHERE zc.a_id = za.id AND data_p < SYSDATE AND zt.id = t_id AND t_id = 6 AND rownum <= 1 UNION   SELECT t_id, body_id, data_w, headline, teaser, name, type FROM zc_body zc, zc_autor za, zc_type zt WHERE zc.a_id = za.id AND data_p < SYSDATE AND zt.id = t_id AND t_id = 7 AND rownum <= 2 UNION   SELECT t_id, body_id, data_w, headline, teaser, name, type FROM zc_body zc, zc_autor za, zc_type zt WHERE zc.a_id = za.id AND data_p < SYSDATE AND zt.id = t_id AND t_id = 8 AND rownum <= 1 UNION   SELECT t_id, body_id, data_w, headline, teaser, name, type FROM zc_body zc, zc_autor za, zc_type zt WHERE zc.a_id = za.id AND data_p < SYSDATE AND zt.id = t_id AND t_id = 21 AND rownum <= 8 UNION   SELECT t_id, body_id, data_w, headline, teaser, name, type FROM zc_body zc, zc_autor za, zc_type zt WHERE zc.a_id = za.id AND data_p < SYSDATE AND zt.id = t_id AND t_id = 2 AND rownum <= 1 UNION   SELECT t_id, body_id, data_w, headline, teaser, name, type FROM zc_body zc, zc_autor za, zc_type zt WHERE zc.a_id = za.id AND data_p < SYSDATE AND zt.id = t_id AND t_id = 35 AND rownum <= 1 UNION   SELECT t_id, body_id, data_w, headline, teaser, name, type FROM zc_body zc, zc_autor za, zc_type zt WHERE zc.a_id = za.id AND data_p < SYSDATE AND zt.id = t_id AND t_id = 34 AND rownum <= 6 )
SELECT t_id, body_id, data_w, headline, teaser, name, type, count(*)   OVER (PARTITION BY t_id ORDER BY data_w DESC) x FROM tbl; Received on Tue Jul 15 2008 - 13:40:55 CDT