SQL sorting while insert possible?

In SQL, while inserting into a pres开发者_C百科orted table, is it possible to insert a row while sorting the row along with the entire table? Such as

create table positional_order (
  num number,
  txt varchar2(10)
);

existing table

row 1   (3, 'three' );
row 2   (4, 'four' );

insert a new row

row 3   (1, 'one');

after the insert

table becomes

row 1   (1, 'one');
row 2   (3, 'three' );
row 3   (4, 'four' );

Is this possible?

Or I have to insert the row into the table and then do select order by ?

Btw I am using sqlite.

Thanks,

---

As far as I know inserting a row into a particular location is not part of any standard SQL database. Part of the database's job is to determine how to logically store rows. As you said, the time to order rows is when you do the SELECT, using ORDER BY.

If you are concerned about performance, create an index on the 'number' column. More on indexes here

---

Short answer: Tables are sets and do not have an order.

Long answer: A clustered index is as close as it comes, and even that isn't really all that close -- w/o an order by, the query engine is free to return the results in any order it thinks best. A clustered index will mean that a query on just that table will most LIKELY return the rows in the order defined for the index, but don't count on it. Now, that said, all indexes define A order and a clustered index defines an order of the records on disk, while this doesn't guarantee your results in any particular order it does make it likely under most circumstances. But I would consider carefully before using a clustered index on anything but an auto generated/incremented column. If you insert into a table with a clustered index, records on disk may have to be relocated, and thats an expensive operation.

---

It's not possible, no. Data in an SQL table isn't actually 'sorted' by default. You'll need to use an ORDER BY clause in your application to get the results in the order you want.

---

I don't think it's possible but you shouldn't need to; as you said, you can either do order by; or define an index on the num column