Re: Union question

From: <fitzjarrell_at_cox.net>
Date: Tue, 15 Jul 2008 12:46:15 -0700 (PDT)
Message-ID: <abf5ea47-12c9-4426-9c61-a3ff25c76914@f36g2000hsa.googlegroups.com>


On Jul 15, 1:40 pm, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> 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;- Hide quoted text -
>
> - Show quoted text -

You can order each individual query, but it will do no good in this situation as the UNIONs will cause the entire result set to be ordered, thus you lose your ordering of each individual result set. Note that each individual result set is ordered by the SAL value, and that collecting them all into one result via UNION completely changes that ordering:

SQL> with emp1 as (

  2  	     select empno, ename, job, sal
  3  	     from emp
  4  	     order by 4

  5 )
  6 select *
  7 from emp1;

     EMPNO ENAME JOB SAL ---------- ---------- --------- ----------

      7369 SMYTHE     CLERK            800
      7935 SMITH      CLERK            900
      7900 JAMES      CLERK            950
      7876 ADAMS      CLERK           1100
      7521 WARD       SALESMAN        1250
      7654 MARTIN     SALESMAN        1250
      7934 MILLER     CLERK           1300
      7844 TURNER     SALESMAN        1500
      7499 ALLEN      SALESMAN        1600
      7782 CLARK      MANAGER         2450
      7698 BLAKE      MANAGER         2850

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7566 JONES      MANAGER         2975
      7788 SCOTT      ANALYST         3000
      7902 FORD       ANALYST         3000
      7839 KING       PRESIDENT       5000

15 rows selected.

SQL>
SQL> with emp2 as(

  2  	     select empno+100 empno, ename, job, sal
  3  	     from emp
  4  	     order by 4

  5 )
  6 select *
  7 from emp2;

     EMPNO ENAME JOB SAL ---------- ---------- --------- ----------

      7469 SMYTHE     CLERK            800
      8035 SMITH      CLERK            900
      8000 JAMES      CLERK            950
      7976 ADAMS      CLERK           1100
      7621 WARD       SALESMAN        1250
      7754 MARTIN     SALESMAN        1250
      8034 MILLER     CLERK           1300
      7944 TURNER     SALESMAN        1500
      7599 ALLEN      SALESMAN        1600
      7882 CLARK      MANAGER         2450
      7798 BLAKE      MANAGER         2850

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7666 JONES      MANAGER         2975
      7888 SCOTT      ANALYST         3000
      8002 FORD       ANALYST         3000
      7939 KING       PRESIDENT       5000

15 rows selected.

SQL>
SQL> with emp3 as(

  2  	     select empno+200 empno, ename, job, sal
  3  	     from emp
  4  	     order by 4

  5 )
  6 select *
  7 from emp3;

     EMPNO ENAME JOB SAL ---------- ---------- --------- ----------

      7569 SMYTHE     CLERK            800
      8135 SMITH      CLERK            900
      8100 JAMES      CLERK            950
      8076 ADAMS      CLERK           1100
      7721 WARD       SALESMAN        1250
      7854 MARTIN     SALESMAN        1250
      8134 MILLER     CLERK           1300
      8044 TURNER     SALESMAN        1500
      7699 ALLEN      SALESMAN        1600
      7982 CLARK      MANAGER         2450
      7898 BLAKE      MANAGER         2850

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7766 JONES      MANAGER         2975
      7988 SCOTT      ANALYST         3000
      8102 FORD       ANALYST         3000
      8039 KING       PRESIDENT       5000

15 rows selected.

SQL>
SQL> with emp4 as(

  2  	     select empno+300 empno, ename, job, sal
  3  	     from emp
  4  	     order by 4

  5 )
  6 select *
  7 from emp4;

     EMPNO ENAME JOB SAL ---------- ---------- --------- ----------

      7669 SMYTHE     CLERK            800
      8235 SMITH      CLERK            900
      8200 JAMES      CLERK            950
      8176 ADAMS      CLERK           1100
      7821 WARD       SALESMAN        1250
      7954 MARTIN     SALESMAN        1250
      8234 MILLER     CLERK           1300
      8144 TURNER     SALESMAN        1500
      7799 ALLEN      SALESMAN        1600
      8082 CLARK      MANAGER         2450
      7998 BLAKE      MANAGER         2850

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7866 JONES      MANAGER         2975
      8088 SCOTT      ANALYST         3000
      8202 FORD       ANALYST         3000
      8139 KING       PRESIDENT       5000

15 rows selected.

SQL>
SQL> with emp1 as (

  2  	     select empno, ename, job, sal
  3  	     from emp
  4  	     order by 4

  5 ),
  6 emp2 as(
  7  	     select empno+100 empno, ename, job, sal
  8  	     from emp
  9  	     order by 4

 10 ),
 11 emp3 as(
 12  	     select empno+200 empno, ename, job, sal
 13  	     from emp
 14  	     order by 4

 15 ),
 16 emp4 as(
 17  	     select empno+300 empno, ename, job, sal
 18  	     from emp
 19  	     order by 4

 20 )
 21 select *
 22 from emp1
 23 union
 24 select *
 25 from emp2
 26 union
 27 select *
 28 from emp3
 29 union
 30 select *
 31 from emp4;

     EMPNO ENAME JOB SAL ---------- ---------- --------- ----------

      7369 SMYTHE     CLERK            800
      7469 SMYTHE     CLERK            800
      7499 ALLEN      SALESMAN        1600
      7521 WARD       SALESMAN        1250
      7566 JONES      MANAGER         2975
      7569 SMYTHE     CLERK            800
      7599 ALLEN      SALESMAN        1600
      7621 WARD       SALESMAN        1250
      7654 MARTIN     SALESMAN        1250
      7666 JONES      MANAGER         2975
      7669 SMYTHE     CLERK            800

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7698 BLAKE      MANAGER         2850
      7699 ALLEN      SALESMAN        1600
      7721 WARD       SALESMAN        1250
      7754 MARTIN     SALESMAN        1250
      7766 JONES      MANAGER         2975
      7782 CLARK      MANAGER         2450
      7788 SCOTT      ANALYST         3000
      7798 BLAKE      MANAGER         2850
      7799 ALLEN      SALESMAN        1600
      7821 WARD       SALESMAN        1250
      7839 KING       PRESIDENT       5000

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7844 TURNER     SALESMAN        1500
      7854 MARTIN     SALESMAN        1250
      7866 JONES      MANAGER         2975
      7876 ADAMS      CLERK           1100
      7882 CLARK      MANAGER         2450
      7888 SCOTT      ANALYST         3000
      7898 BLAKE      MANAGER         2850
      7900 JAMES      CLERK            950
      7902 FORD       ANALYST         3000
      7934 MILLER     CLERK           1300
      7935 SMITH      CLERK            900

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7939 KING       PRESIDENT       5000
      7944 TURNER     SALESMAN        1500
      7954 MARTIN     SALESMAN        1250
      7976 ADAMS      CLERK           1100
      7982 CLARK      MANAGER         2450
      7988 SCOTT      ANALYST         3000
      7998 BLAKE      MANAGER         2850
      8000 JAMES      CLERK            950
      8002 FORD       ANALYST         3000
      8034 MILLER     CLERK           1300
      8035 SMITH      CLERK            900

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      8039 KING       PRESIDENT       5000
      8044 TURNER     SALESMAN        1500
      8076 ADAMS      CLERK           1100
      8082 CLARK      MANAGER         2450
      8088 SCOTT      ANALYST         3000
      8100 JAMES      CLERK            950
      8102 FORD       ANALYST         3000
      8134 MILLER     CLERK           1300
      8135 SMITH      CLERK            900
      8139 KING       PRESIDENT       5000
      8144 TURNER     SALESMAN        1500

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      8176 ADAMS      CLERK           1100
      8200 JAMES      CLERK            950
      8202 FORD       ANALYST         3000
      8234 MILLER     CLERK           1300
      8235 SMITH      CLERK            900

60 rows selected.

SQL> So, yes, you can use the WITH construct to create umpteen individual queries, each ordered meticulously, but when you pile all of the individual results together through UNIONS your order isn't what you expect.

Why do you 'need' each result set ordered separately? What business purpose does that serve? It appears your 'need' is a waste of effort.

David Fitzjarrell Received on Tue Jul 15 2008 - 14:46:15 CDT

Original text of this message