Re: Union question

From: Terry Dykstra <tddykstra_at_forestoil.ca>
Date: Wed, 16 Jul 2008 17:38:53 GMT
Message-ID: <Nmqfk.350$Ou3.127@edtnps82>


<amerar_at_iwc.net> wrote in message
news:dd91c7aa-1e94-4352-86cc-6294ca2fba7c_at_k30g2000hse.googlegroups.com... On Jul 16, 8:28 am, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> On Jul 16, 7:52 am, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
>
>
>
>
>
> > On Jul 15, 4:41 pm, sybra..._at_hccnet.nl wrote:
>
> > > On Tue, 15 Jul 2008 10:06:31 -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??
>
> > > Just repost the same crap as you don't get the responses which suit
> > > you, eh?
> > > Did you actually read the responses you got so far at all? Or do you
> > > simply already 'know' how to resolve it? In that case, why post a
> > > question?
> > > So, reading your previous insults in my direction, who is the butthead
> > > and the jackass?
> > > It appears it is NOT me. It appears you are simply an incompetent
> > > arrogant little twit. Oh well.
>
> > > PLOINK!!!
>
> > > --
>
> > > Sybrand Bakker
> > > Senior Oracle DBA
>
> > A typical comment from someone who really thinks that they are better
> > than anyone else.
>
> Irritation is not arrogance, much to your dismay, I expect. That you
> choose to not comprehend the responses thus far and continue to post
> in hopes of receiving your desired response is, without question,
> irritating.
>
> > I'd understand if I want the only one who you gave this attitude
> > towards. But I've seen you reply to others with the same
> > disrespectful asshole like attitude, indicating that you were probably
> > sexually abused by your father as a child and can no longer function
> > as a part of the human race.
>
> Such 'commentary' is unnecessary, and unwarranted given the fact it is
> you who are continuing to post the same question over and over and
> over. You have absolutely no proof of your 'claims' except for your
> skewed perception, which isn't a proper evaluation. You should
> retract this slander immediately.
>
> > Now, given that an entire union is ordered, I'm just looking for a way
> > to union these queries, but have each query retain it's own sort
> > order. If this really cannot be done,
>
> Which has been proven in this thread. Unless you alter your select
> lists to include an 'ordering' value and thus include any duplicate
> records in your result set (which does not happen with your data
> currently in this UNION) it cannot be done. It's a shame youc can't,
> or won't, realise this.
>
> > then perhaps I can add all the
> > results to a collection, as long as I can pass that back to a PHP
> > script and read it properly from PHP.
>
> You are generating this result set 'properly' given that you've asked
> Oracle to UNION your individual results and, thus, it sorts the data
> in order to remove duplications. Of course if you WANT your
> duplicates in the result set then UNION ALL does exactly what you ask:
>
> 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 all
> 24 select *
> 25 from emp2
> 26 union all
> 27 select *
> 28 from emp3
> 29 union all
> 30 select *
> 31 from emp4;
>
> 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
> 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
>
> EMPNO ENAME JOB SAL
> ---------- ---------- --------- ----------
> 7944 TURNER SALESMAN 1500
> 7599 ALLEN SALESMAN 1600
> 7882 CLARK MANAGER 2450
> 7798 BLAKE MANAGER 2850
> 7666 JONES MANAGER 2975
> 7888 SCOTT ANALYST 3000
> 8002 FORD ANALYST 3000
> 7939 KING PRESIDENT 5000
> 7569 SMYTHE CLERK 800
> 8135 SMITH CLERK 900
> 8100 JAMES CLERK 950
>
> EMPNO ENAME JOB SAL
> ---------- ---------- --------- ----------
> 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
> 7766 JONES MANAGER 2975
> 7988 SCOTT ANALYST 3000
> 8102 FORD ANALYST 3000
>
> EMPNO ENAME JOB SAL
> ---------- ---------- --------- ----------
> 8039 KING PRESIDENT 5000
> 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
>
> EMPNO ENAME JOB SAL
> ---------- ---------- --------- ----------
> 7998 BLAKE MANAGER 2850
> 7866 JONES MANAGER 2975
> 8088 SCOTT ANALYST 3000
> 8202 FORD ANALYST 3000
> 8139 KING PRESIDENT 5000
>
> 60 rows selected.
>
> SQL>
>
> A conventional UNION, however, will never return data as you desire.
>
> > I'll have to look into that.- Hide quoted text -
>
> > - Show quoted text -
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

Well, then I think I'll have to store it in a table and see if I can return it to PHP.

There will never be duplicates in the results as each select has a different criteria which cannot result in duplicates.....

Thanks for your help and patience.

If you know for sure that there will be no duplicates, then you should be using UNION ALL, to eliminate the extra sorting that Oracle will perform when using just a UNION.
Storing in a table is not going to help. When you do a select from a table you are not guaranteed the order in which records are being returned. I believe that my earlier suggestion of adding a sort indicator would work in your case.

-- 
Terry Dykstra 
Received on Wed Jul 16 2008 - 12:38:53 CDT

Original text of this message