Storing data in non alphabetical order

问题:

I am storing values into a table with an SQL INSERT query

Query:

INSERT INTO tbl_Deta ([ID NO], [OR NO], [ITEM DESCRIPTION], QUANTITY, UNIT)
VALUES (6,0,'ring gauge',44,nos)

INSERT INTO tbl_Deta ([ID NO], [OR NO], [ITEM DESCRIPTION], QUANTITY, UNIT)
VALUES (6,0,'asd',14,ads)

INSERT INTO tbl_Deta ([ID NO], [OR NO], [ITEM DESCRIPTION], QUANTITY, UNIT)
VALUES (6,0,'amd',33,asd)

Result:

[ID NO]  [OR NO]  [ITEM DESCRIPTION]  QUANTITY  UNIT
  6        0             asd             14     ads 
  6        0            ring gauge       44     Nos 
  6        0             amd             33     asd 

I want result in the same order in which the data are inserted and not in the alphabetical order.

回答1:

There is no inherent order inside a table.

There is no pre-stablished order when you insert rows. If you do a

SELECT [ID NO], [OR NO], [ITEM DESCRIPTION], QUANTITY, UNIT FROM tbl_Deta

Without an ORDER BY then no particular order will be used. If you want some particular sorted results, you must use an ORDER BY clause. You could use a primary key and sort according to its values.

There are several questions/answers that could help you to get a better understanding of this particular point:

• You cannot guarantee the order unless you specifically provide an order by with the query
• In the SQL world, order is not an inherent property of a set of data.
• and a really deep and detailed answer on dba.stackexchange.com: Storage order vs Result order

I was just reading through my RSS feeds and found an awesome blog entry by Michael J. Swart. All the best answers on this subject in one entry, great!

回答2:

Retrieving rows in the same order they were inserted is not a standard guaranteed SQL feature. I suggest adding another column that you can order by, for example an identity/autonumber integer field, or perhaps a date inserted.

回答3:

If I am imagining your order entry system correctly, you probably have a list of items in that order id. I encourage you to have an ITEM ID column, but you have to ORDER BY when you SELECT. As an added benefit this ITEM ID helps you, in case you need to UPDATE.

回答4:

how is your table actually looking? You can look this up like this:

SHOW CREATE TABLE tbl_Deta;

If you have a column storing creation date (typically named "created_at"), you can easily order by creation date:

SELECT * FROM tbl_Deta order by created_at;

However, as mentioned in the comments, an auto-incremented field might be the better way to go.