Getting result in order from Select query.....

Database
Enthusiast

Getting result in order from Select query.....

Hello,

I have created table as

CREATE TABLE TESTINDEX(EMPNO DECIMAL(18,0), EMPNAME VARCHAR(100)) UNIQUE PRIMARY INDEX (EMPNO);

following records are inserted into to TESTINDEX

INSERT 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 TEST2

But in case of SQL Server it is returning

EMPNO EMPNAME
1 TEST1
2 TEST2
3 TEST3
4 TEST4
5 TEST5

Since 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).

6 REPLIES
Enthusiast

Re: Getting result in order from Select query.....

Hi
When i am running your query in my system(340 AMP)
I am getting result as
EMPNO EMPNAME
2 TEST2
4 TEST4
1 TEST1
3 TEST3
5 TEST5

Which is totally different from yours
There is no definite pattern for this ....an ALL AMP operation like this will scan all tables put the data into spool as in EXPLAIN plan
3) We do an all-AMPs RETRIEVE step from TESTINDEX by way
of an all-rows scan with no residual conditions into Spool 1
(group_amps), which is built locally on the AMPs. The size of
Spool 1 is estimated with low confidence to be 340 rows. The
estimated 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.........
Enthusiast

Re: Getting result in order from Select query.....

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.
Enthusiast

Re: Getting result in order from Select query.....

Hi Jim
It 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 EMPNAME
2 TEST2
4 TEST4
1 TEST1
3 TEST3
5 TEST5

that means there shud be some internal ordering that takes place....
Please clarify...
Teradata Employee

Re: Getting result in order from Select query.....

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

Re: Getting result in order from Select query.....

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

Enthusiast

Re: Getting result in order from Select query.....

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.