Re: Union question
Date: Wed, 16 Jul 2008 10:46:45 -0700 (PDT)
Message-ID: <4abba8c4-bb15-4fd1-a049-0468f3f0ad2c@m3g2000hsc.googlegroups.com>
On Jul 16, 11:52 am, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
Then I've provided a solution for you in my prior response, even though it contains the caveat regarding duplicate data. The example I posted has no duplicates, and the output meets your criteria. It's a shame you can't read an example.
David Fitzjarrell. Received on Wed Jul 16 2008 - 12:46:45 CDT