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??

SELECT t_id, body_id, data_w, headline, teaser, name FROM zc_body zc,
zc_autor za WHERE zc.a_id = za.id AND data_p < SYSDATE AND t_id = 43
AND rownum <= 1
UNION
SELECT t_id, body_id, data_w, headline, teaser, name FROM zc_body zc,
zc_autor za WHERE zc.a_id = za.id AND data_p < SYSDATE AND t_id = 44
AND rownum <= 3
UNION
SELECT t_id, body_id, data_w, headline, teaser, name FROM zc_body zc,
zc_autor za WHERE zc.a_id = za.id AND data_p < SYSDATE AND t_id = 45
AND rownum <= 2
UNION
SELECT t_id, body_id, data_w, headline, teaser, name FROM zc_body zc,
zc_autor za WHERE zc.a_id = za.id AND data_p < SYSDATE AND t_id = 6
AND rownum <= 7
UNION
SELECT t_id, body_id, data_w, headline, teaser, name FROM zc_body zc,
zc_autor za WHERE zc.a_id = za.id AND data_p < SYSDATE AND t_id = 7
AND rownum <= 1
UNION
SELECT t_id, body_id, data_w, headline, teaser, name FROM zc_body zc,
zc_autor za WHERE zc.a_id = za.id AND data_p < SYSDATE AND t_id = 8
AND rownum <= 2
UNION
SELECT t_id, body_id, data_w, headline, teaser, name FROM zc_body zc,
zc_autor za WHERE zc.a_id = za.id AND data_p < SYSDATE AND t_id = 21
AND rownum <= 1
UNION
SELECT t_id, body_id, data_w, headline, teaser, name FROM zc_body zc,
zc_autor za WHERE zc.a_id = za.id AND data_p < SYSDATE AND t_id = 2
AND rownum <= 3
UNION
SELECT t_id, body_id, data_w, headline, teaser, name FROM zc_body zc,
zc_autor za WHERE zc.a_id = za.id AND data_p < SYSDATE AND t_id = 35
AND rownum <= 1
UNION
SELECT t_id, body_id, data_w, headline, teaser, name FROM zc_body zc,
zc_autor za WHERE zc.a_id = za.id AND data_p < SYSDATE AND t_id = 34
AND rownum <= 8
ORDER BY data_w DESC;


Thanks all!!!

On Tue, 15 Jul 2008 10:06:31 -0700 (PDT), "amerar@iwc.net"
<amerar@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

On Jul 15, 4:41*pm, sybra...@hccnet.nl wrote:
> On Tue, 15 Jul 2008 10:06:31 -0700 (PDT), "ame...@iwc.net"
>
> <ame...@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.

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.


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, 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. I'll have to look into that.

On Jul 16, 7:52*am, "ame...@iwc.net" <ame...@iwc.net> wrote:
> On Jul 15, 4:41*pm, sybra...@hccnet.nl wrote:
>
>
>
>
>
> > On Tue, 15 Jul 2008 10:06:31 -0700 (PDT), "ame...@iwc.net"
>
> > <ame...@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

On Jul 16, 8:28*am, "fitzjarr...@cox.net" <orat...@msn.com> wrote:
> On Jul 16, 7:52*am, "ame...@iwc.net" <ame...@iwc.net> wrote:
>
>
>
>
>
> > On Jul 15, 4:41*pm, sybra...@hccnet.nl wrote:
>
> > > On Tue, 15 Jul 2008 10:06:31 -0700 (PDT), "ame...@iwc.net"
>
> > > <ame...@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.

<amerar@iwc.net> wrote in message
news:dd91c7aa-1e94-4352-86cc-6294ca2fba7c@k30g2000hse.googlegroups.com...
On Jul 16, 8:28 am, "fitzjarr...@cox.net" <orat...@msn.com> wrote:
> On Jul 16, 7:52 am, "ame...@iwc.net" <ame...@iwc.net> wrote:
>
>
>
>
>
> > On Jul 15, 4:41 pm, sybra...@hccnet.nl wrote:
>
> > > On Tue, 15 Jul 2008 10:06:31 -0700 (PDT), "ame...@iwc.net"
>
> > > <ame...@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.



If you know for sure that there will be no duplicates, then you should be
using UNION ALL, to eliminate the extra sorting that Oracle will perform
when using just a UNION.
Storing in a table is not going to help. When you do a select from a table
you are not guaranteed the order in which records are being returned. I
believe that my earlier suggestion of adding a sort indicator would work in
your case.

--
Terry Dykstra

On Jul 16, 11:52*am, "ame...@iwc.net" <ame...@iwc.net> wrote:
> On Jul 16, 8:28*am, "fitzjarr...@cox.net" <orat...@msn.com> wrote:
>
>
>
>
>
> > On Jul 16, 7:52*am, "ame...@iwc.net" <ame...@iwc.net> wrote:
>
> > > On Jul 15, 4:41*pm, sybra...@hccnet.nl wrote:
>
> > > > On Tue, 15 Jul 2008 10:06:31 -0700 (PDT), "ame...@iwc.net"
>
> > > > <ame...@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 orderby.
>
> > > > >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.

On Jul 16, 12:46*pm, "fitzjarr...@cox.net" <orat...@msn.com> wrote:
> On Jul 16, 11:52*am, "ame...@iwc.net" <ame...@iwc.net> wrote:
>
>
>
>
>
> > On Jul 16, 8:28*am, "fitzjarr...@cox.net" <orat...@msn.com> wrote:
>
> > > On Jul 16, 7:52*am, "ame...@iwc.net" <ame...@iwc.net> wrote:
>
> > > > On Jul 15, 4:41*pm, sybra...@hccnet.nl wrote:
>
> > > > > On Tue, 15 Jul 2008 10:06:31 -0700 (PDT), "ame...@iwc.net"
>
> > > > > <ame...@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.