Error when I try to create ordered SQL table

Question

I'm trying to create a volatile table in SQL with an ORDER BY and I get an error.

CREATE VOLATILE TABLE orderd_dates AS
(SELECT * FROM date_table
ORDER BY id_date)
with data primary index (id_date) on commit preserve rows;

The error is: ORDER BY is not allowed in subqueries.

If I can't use order by, how can I create a volatile table that's ordered?

Answer 1

SQL tables are inherently unordered. You need to explicitly use an order by clause when querying the table, not when creating it.