Problem with Order by in Volatile Tables

Database
Enthusiast

Problem with Order by in Volatile Tables

Hi,
I have a problem with the next query:

CREATE MULTISET VOLATILE TABLE TEMP AS
(SELECT .....
FROM ......
WHERE ......
GROUP BY ....
ORDER BY ....)
WITH DATA ON COMMIT PRESERVE ROWS

Because when is executed return the next error:
3706: Syntax error: ORDER BY is not allowed in subqueries

Can be changed this query to allow the "order by" command or is it impossible?

Thank you very much.
Greetings.
5 REPLIES
Enthusiast

Re: Problem with Order by in Volatile Tables

I'm not sure what you're trying to accomplish with an "ORDER BY". If you are trying to keep the rows in that order in the table, that is contrary to the way Teradata stores data in a table. Teradata distributes the rows according to the hash value of the primary index and then keeps the rows in hash order within each data block.

I would suggest that you remove the "ORDER BY" add a "PRIMARY INDEX" clause to your statement. Otherwise, Teradata will make the first column of the table the primary index, which may not be a good choice.
Enthusiast

Re: Problem with Order by in Volatile Tables

Thanks for your reply.

I need to order by Date and other columns.

The problem is also that in the same table, I will execute distinct querys with distinct columns in the order by, so I need the order by sentence.

For example:

Query 1: from table 1 ... order by DATE, Column2, Column3
Query 2: from table 1 ... order by DATE, Column3
Query 3: from table 1 ... order by Column2

I think that I can't add a Primary Index for this.

Thanks.
Enthusiast

Re: Problem with Order by in Volatile Tables

Sorry..
I think that I understood your idea.

Can I build the query:
"WITH DATA
PRIMARY INDEX (DATE, columna1, columna2...)
ON COMMIT PRESERVE ROWS" ¿?

This sentence order as an "order by DATE, columna1, columna2..)" ?

Thank you very much!
Enthusiast

Re: Problem with Order by in Volatile Tables

You shouldn't select your primary index based on the "ORDER BY" you will be specifying in your queries. Teradata always sorts the rows to accomplish the "ORDER BY" in a query. Unlike other DBMS's you may have worked with, the sorting on Teradata is very fast because each AMP sorts only it's rows and then a "merge" is done to merge the sorted rows from all of the AMPs.

Choose the primary index based on either a unique or somewhat unique column or set of columns. As a very general rule of thumb, a particular primary index value should point to a few hundred rows or less.

Teradata has a "value-ordered" index (it's a type of secondary index, not a primary index option), but it is only used for the selection of rows (i.e. range selections), not the sorting of rows. So, even a value-ordered index would not help you in this case.
Enthusiast

Re: Problem with Order by in Volatile Tables

OK. Thanks