Re: Union question

From: <fitzjarrell_at_cox.net>
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

Original text of this message