All Forums Database
09 Jun 2009
Getting result in order from Select query.....

Hello,I have created table asCREATE TABLE TESTINDEX(EMPNO DECIMAL(18,0), EMPNAME VARCHAR(100)) UNIQUE PRIMARY INDEX (EMPNO);following records are inserted into to TESTINDEXINSERT INTO TESTINDEX VALUES(5,'TEST5');INSERT INTO TESTINDEX VALUES(3,'TEST3');INSERT INTO TESTINDEX VALUES(2,'TEST2');INSERT INTO TESTINDEX VALUES(4,'TEST4');INSERT INTO TESTINDEX VALUES(1,'TEST1');Query SELECT * FROM TESTINDEX ;give result EMPNO EMPNAME 5 TEST5 3 TEST3 1 TEST1 4 TEST4 2 TEST2But in case of SQL Server it is returning EMPNO EMPNAME 1 TEST1 2 TEST2 3 TEST3 4 TEST4 5 TEST5Since It is due to clustered index default behaviour.Question is that, I need the Teradata's SELECT query behaviour as like SQL Server's SELECT query behaviour without using ORDER BY clause in SELECT query of Teradata. ( Is this possible by using Index).

Teradata_new 12 posts Joined 06/09
09 Jun 2009

HiWhen i am running your query in my system(340 AMP)I am getting result asEMPNO EMPNAME2 TEST24 TEST41 TEST13 TEST35 TEST5Which is totally different from yoursThere is no definite pattern for this ....an ALL AMP operation like this will scan all tables put the data into spool as in EXPLAIN plan3) We do an all-AMPs RETRIEVE step from TESTINDEX by wayof an all-rows scan with no residual conditions into Spool 1(group_amps), which is built locally on the AMPs. The size ofSpool 1 is estimated with low confidence to be 340 rows. Theestimated time for this step is 0.01 seconds.After assembling rows into spool they are sent back to user...it depends on how many nodes you have+AMP having data for that table...Although you can always use order by on empno to get desired result...I am not sure how this default numbering really happens may be FORUM GURUS can help us out in that.........

Jim Chapman 449 posts Joined 09/04
09 Jun 2009

Tables don't have any inherent order. You must use ORDER BY in your select if you want the result to be in a particular order.

Teradata_new 12 posts Joined 06/09
09 Jun 2009

Hi JimIt is true that table doesn't have any order...But every time we ran a particular query same seq of data is returned although it is not order...like in this case EMPNO EMPNAME2 TEST24 TEST41 TEST13 TEST35 TEST5that means there shud be some internal ordering that takes place....Please clarify...

Fred 1096 posts Joined 08/04
09 Jun 2009

The sequence appears to be constant because this is such a simple example. In the general case, you cannot rely on any implicit ordering.

Adeel Chaudhry 773 posts Joined 04/08
09 Jun 2009

Hello,Briefly, following are the observations:- Tables are not stored in particular order (just as Jim Chapman/Fred Pluebell replied)- Without specifying ORDER BY clause, you will not get data in particular order always (just as Jim Chapman/Fred Pluebell replied)- Same data will be retrieved in different order on different systems having different number of AMPs- Data is fetched and displayed on first-come-first-serve basis, if AMP-10 gets the 1st row, it will be displayed at the top, if AMP-1 gets the 1st row, it will be displayed at the top, unless off-course you specify the ORDER BY clause explicitly!HTH!Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Sunar 59 posts Joined 02/08
11 Jun 2009

Could you please clarify your requirement, that why you don't want to use order by clause in your select query ?So that we might look for some possible solution.

You must sign in to leave a comment.