Re: Union question
Date: Tue, 15 Jul 2008 19:52:40 GMT
Message-ID: <ce7fk.384$rR2.206@edtnps83>
"fitzjarrell_at_cox.net" <oratune_at_msn.com> wrote in message
news:abf5ea47-12c9-4426-9c61-a3ff25c76914_at_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
If you really need the results to be in a specific order, I would add an extra sort indicator:
select '1' sortcode, colx, ...
from mytable
union
select '2' sortcode, colx, ...
from mytable
select '3' sortcode, colx, ...
from mytable
-- Terry DykstraReceived on Tue Jul 15 2008 - 14:52:40 CDT