Storing data in non alphabetical order

Question

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.

Try adding an auto increment field to the table and sort by that field when querying the table. — Ihor Romanchenko, May 05 '13 at 11:25
Did some of the answers answered your question? If yes, please accept the answer so others can check it out. — Yaroslav, Aug 07 '13 at 17:24

score 9 · Answer 1 · edited May 23 '17 at 12:25

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!

score 6 · Answer 2 · answered May 05 '13 at 11:25

6

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.

answered May 05 '13 at 11:25

David Cummins

972
6
15

I tried both Adding auto increment field and querring database without order by claus but both did't worked – Rohan May 05 '13 at 11:30
2

There is no inherent order inside a table @Rohan, if you do not include `ORDER BY` then there is no guarantee that you will get same order as you inserted the rows, even with an autoincrement field. You must use `ORDER BY` to get the desired order – Yaroslav May 05 '13 at 11:33

mseebacher · Answer 3 · 2013-05-16T20:39:16.937

1

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.

edited May 16 '13 at 20:39

answered May 05 '13 at 11:27

mseebacher

4,931
1
16
11

Be careful of using a local date/time for ordering. Every now and then some silly governmental body may come along and decide that you need to relive an hour here or skip one there. – HABO May 05 '13 at 13:36

score 1 · Answer 4 · answered May 05 '13 at 16:02

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.

Storing data in non alphabetical order

4 Answers4

Linked