Re: Union question
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