I have a table (with no PK) that is being inserted into by an regular event. It is currently unordered, and I would like to order it in the fashion of
select * from t1 order by col1 ASC,col2 DESC
Constraints are as follows:
For performance reasons I can't create a new temporary table to store the ordered data in
For cleanness of the user-interface I must assume that all select statements will be without an order by clause
Because of the insert into mechanism I can't order the table while it's being built up
Is there some way of doing this that I don't know about/have overlooked?
Is there some way of doing this that I don't know about/have overlooked?
No. And here is a very good explanation. Quoting the most revelant part:
In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause.
That being said, if you remove your performance requirements, you could create an ordered view and select from it. For example (not sure about the syntax):
CREATE VIEW foobar AS
select * from t1 order by col1 ASC,col2 DESC;
But it's going to be slow(ish).
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments