Re: Union question
Date: Wed, 16 Jul 2008 06:28:51 -0700 (PDT)
Message-ID: <347225e8-2d6f-49d7-9a8a-d58b3626e288@25g2000hsx.googlegroups.com>
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 Received on Wed Jul 16 2008 - 08:28:51 CDT