|
View previous topic :: View next topic
|
| Author |
Message |
arif.md
New User
Joined: 13 Jun 2005
Posts: 10
|
|
|
|
I have same set of data in Production and Test region in two tables for a particular Employee.
Now when I am selecting columns1, 2 and 3 from the Table1 and it has a join on Table2.
The data selected is not in the same order in both regions. I am not supposed to use ORDER BY clause here as it will affect the other data.
Query I have used.
SELECT
T2.column1,
T3.column1,
T3. Column2
FROM Table1 T1
,Table2 T2
,Table2 T3
WHERE T2.A_ID = T1.A_ID
AND T2.CANC_DT = '12/31/9999'
AND T2.A_TYP_CD IN ('L', 'D')
AND T2.BAID = T3.A_ID
AND T2.BAID_TYP = T3.A_TYP_CD
AND T2.P_ID = T3.P_ID
AND T2.T_ID = T3.T_ID
AND T2.T_TYP_CD = T3.T_TYP_CD
AND T2.P_ID = :Numeric Value
AND T1.Z_CD = :Numeric Value
AND T1.Z_4_CD = :Numeric Value
The above query is showing data in different order in different regions.
Thanks
Arif |
|
| Back to top |
|
 |
William Thompson
Global Moderator
Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
|
|
|
|
That's the breaks.....
order-by-clause
| Quote: |
| If you do not specify ORDER BY, the rows of the result table have an arbitrary order. |
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
If you do not specify ordering criteria, the system will only return the rows you asked for - in no predictable order.
Where does a requirement come from that demands a sequence, but permission to put the rows in sequence is denied?
Obviously, there is more involved than has been posted. . . |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
But but but.... I need my database queries to always be in the same order unless I use the ORDER BY to put them in a different order. 
As William said, thems the breaks. That damn optimizer to improve performance.... |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| stodolas wrote: |
But but but.... I need my database queries to always be in the same order unless I use the ORDER BY to put them in a different order.
As William said, thems the breaks. That damn optimizer to improve performance.... |
Without an order by there is no way to know what the order will be. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
I just re-read the original post and this
| Quote: |
I am not supposed to use ORDER BY clause here as it will affect the other data.
|
makes me ask - how will it "affect the other data"?
If you describe what your process should do, we may be able to offer suggestions. |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| Craq: apparently I didn't drip enough sarcasm into my typing |
|
| Back to top |
|
|
bhushan.shete
New User
Joined: 14 Nov 2006
Posts: 17
|
|
|
|
| Quote: |
| "If you do not specify ORDER BY, the rows of the result table have an arbitrary order." |
Is this mentioned in manual? I could not find it.
Suppose if we have employee table with empno as primary key (unique index). If i donot mention ORDER BY then, will order of output rows in order of empno (i.e. as per primary key).
In other words, if EMPNO is primary key will output of below queries different?
1.
| Code: |
| SELECT EMPNO FROM EMPLOYEE; |
2.
| Code: |
| SELECT EMPNO FROM EMPLOYEE ORDER BY EMPNO; |
|
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
After 4 1/2 years, I don't suppose anyone is interested in suddenly taking-up that conversation again.
Have you tried it out? You can always post as a new topic if you have a question/useful piece of information, and can always link to the original topic (look at the URL button). |
|
| Back to top |
|
|
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
| I think it depends whether the index is a clustering index or not. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| bhushan.shete wrote: |
| Quote: |
| "If you do not specify ORDER BY, the rows of the result table have an arbitrary order." |
Is this mentioned in manual? I could not find it.
|
Looking back through the topic, I suppose you could try the link just a little above the text that you quoted? |
|
| Back to top |
|
|
|
|
