Specifying a sort order while creating a table

Database

Specifying a sort order while creating a table

Hi,

I am trying to create a simple temp table and would like to set a sort order for how the data is displayed when I select from that table. I am currently using the following syntax:

create table myschema.medical_center_list
(Med_ctr_abbr varchar (254) primary key not null ,
Medical_center_name varchar (254)
)

I would like to specify that the data should be sorted by the med_ctr_abbr in ascending order when I do a select * from this table.

How do I do that? Where should I specify this sort order?

Thank you .
5 REPLIES
N/A

Re: Specifying a sort order while creating a table

It goes at the end of the sql when you run a query.

You cannot specify a sort order on a table or view - only when you run against the table or view!

Re: Specifying a sort order while creating a table

If sort order is not possible while creating a table, how can I make sure that the rows in the table display in the same order in which I insert the data. For example, the rows in my flat file are in the order A,B,C,D,E and F when I insert into the table. However, when I select * from the table, the rows are in a random order say B,E,F,A,C,D.

What should I specify in order to display the data in the same order in which it was inserted. The conditon probably goes into the Insert clause?
Teradata Employee

Re: Specifying a sort order while creating a table

Teradata does not maintain rows in the recieved order. Each AMP maintain its rows by the rowhash ascending order.
If you want to maintain any specific order for display, you must create an additional column which keep the wanted order.
note that you must create this numbers outside of the database.
Then for displaying the data according to your specific ordering , you must user the ORDER BY clause in the select statement.

Re: Specifying a sort order while creating a table

Thank you for your response. Atleast I won't waste more time trying to figure this out.

If that is the case, I'll probably try creating another table and insert date into that table using a select statement from the the first table that I created, sorted by the column that I want. I hope atleast that will work.

Thank you all for your insight on this question.
N/A

Re: Specifying a sort order while creating a table

You cannot specify a sort order on an Insert or any other action query.
So no, that will not work either!