Re: Union question

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. Received on Wed Jul 16 2008 - 11:52:30 CDT