Re: Union question

On Jul 16, 12:46 pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

David,

My apologies. We've all been under a lot of pressure here to get this done, and I've been reading too fast. Your solution seems to work and I thank you for your time and efforts. Received on Wed Jul 16 2008 - 13:10:30 CDT