1

When I do "SELECT * FROM table" where does the order from the rows come from and is there a way to alter it?

Story: I have this site which retrieves dresses sizes and needs a size chart update. The order is a tricky thing, since a general rule is probably a preg_match which I'm not willing or having the enough skill to create (Order should be: Number ASC (00 before 0) and then after the first number varchar ASC).

Next option is "add order column", but it costs more than updating.

So I start updating and the first ones I insetred were showing first. Today they were shown last. Now they show first again. Also order maintains upon update. So it's not "date created" nor "date updated".

UPDATE: found how to rearrange order in PHPMyAdmin under "Alter table order by" in "options" table's tab, which solves my current problem but still wondering which the order is if not altered.

JoaquinLarra
  • 117
  • 4
  • 13
  • If you want a particular listing order then you will have to use an explicit `ORDER BY` clause, possibly involving a UDF to generate a suitable sort string from the available data (from one or several columns). – Carsten Massmann Dec 04 '15 at 17:11
  • Your question is not at all clear. – Strawberry Dec 04 '15 at 17:14
  • @lad2015: I don't think OP wants to change the order of columns in the table. Instead he wants to make sure a certain "pre-defined sorting order" will be applied whenever a `SELECT` is done on the table. – Carsten Massmann Dec 04 '15 at 17:15
  • not a duplicate of rearrange columns. Exactly what @cars10 says. UPDATE: found how to rearrange in PHPMyAdmin under "Alter table order by", which solves half the problem. – JoaquinLarra Dec 04 '15 at 17:22
  • According to the MySQL documentation, `ALTER TABLE ORDER BY` is used for optimizing a table. It does not guarantee that queries without a specified ORDER BY will use that; and while I would guess that at first the rows would come back in that order, I would be surprised if that order didn't "break" the next time something was inserted. – Uueerdo Dec 04 '15 at 17:33

4 Answers4

1

According to this DBA Exchange answer https://dba.stackexchange.com/questions/6051/what-is-the-default-order-of-records-for-a-select-statement-in-mysql

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.

MySQL sorts the records however it wants without any guarantee of consistency.

But observing the store system algoritm seem according to the storage sequence ..

But for to be sure you must specify your desired order using ORDER BY. To do anything else is to set yourself up for unwelcome surprises.

Referring to the order of the row result then the order where no order by clause is defined is unpredictable by ANSI Definition of SQL. The rows are retrieved form the "storage system" whitout regarding a specific strategy. depende of the allocation algoritm of the space disk and the mapping of the segmentation on this disk space (tablespace)

Referring to the column sequence in select (*) these are show based on the position id in the column table

Community
  • 1
  • 1
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • ORDER BY is not an option, since it'll require a set of rules difficult to define. Order seems consistent: without any indications it maintained thru 2 servers. But I don't know which order rule applies as default. Since there is "Alter table order by" which alters the (ommited) natural order by this is stored somewhere. – JoaquinLarra Dec 04 '15 at 17:26
  • Yes is not an option but in the SQL word the no order by sorting in impredictable for ANSI definition – ScaisEdge Dec 04 '15 at 17:28
  • This came for SQL ANSI foundament – ScaisEdge Dec 04 '15 at 17:29
  • The Alter table order by is related to the column, and the order column is mapped in database dictionary /schema .. You can get this by query on the table/column dictionary – ScaisEdge Dec 04 '15 at 17:31
  • If is this you are looking for i can update the answer.. let me know.. – ScaisEdge Dec 04 '15 at 17:31
  • I'm aware of the implications of having the statements as they are. But system was working this way from it's birth a couple of years ago and this small update is not worth any structure change but some rows in a table. To put it another way: best practices are being impleted but the cost of change requires not always doing it. If all applied at same time client runs away. WITH reason. So I understand the "No guarantee" policy but this is certainly defined somewhere. – JoaquinLarra Dec 04 '15 at 17:34
  • which query retreives the order applied? (if order is not defined) – JoaquinLarra Dec 04 '15 at 17:36
  • 1
    If you refer to the order of the row result then the order where no order by clause is defined is unpredictable by ANSI Definition of SQL. The rows are retrieved form the "storage system" whitout regarding a specific strategy. depende of the allocation algoritm of the space disk and the mapping of the segmentation on this disk space (tablespace) – ScaisEdge Dec 04 '15 at 17:42
  • If you referreing to the column sequence in select this in base on the position id in the column table – ScaisEdge Dec 04 '15 at 17:43
  • Not the column secuence. Rows are retrieved in a default order if no "order by" is defined. And this order is not random, it is defined somewhere and it can be altered. – JoaquinLarra Dec 09 '15 at 14:10
0

When you do select * ... youll get the order the table was created with.

Alternativly, you can do select col2, col1, col3 .... to get other column order you want

Nir Levy
  • 12,750
  • 3
  • 21
  • 38
0

Without an ORDER BY clause there is no specific order to results. Would ORDER BY FIELD help you at all? With that you can specify an exact order when ordering that field as an example

ORDER BY FIELD(dress_size, '00', '0', '1', 'AA', 'A','G', '4')

where you specify an exact order for that particular column to sort.

Niagaradad
  • 453
  • 3
  • 10
0

ALTER TABLE statements change the structure of the table, they do not handle the contents of the columns/rows themselves. So you use ALTER to change data types or add a new column etc. ALTER is not what you are looking for.

Niagaradad
  • 453
  • 3
  • 10