1

I created a table and inserted values into it but they are not storing in orderly manner.
The table looks like:
data grid

I can order them by using ORDER BY but it does not change them permanently. Is there a way to save them in orderly manner permanently.

Clarification: I don't want how they are stored internally, what I want is when ever I execute select * from cricket; the results to come sequentially. So that I don't want to use ORDER BY clause every time. Is there a way?

ypercubeᵀᴹ
  • 92,106
  • 13
  • 189
  • 284
dvr
  • 11
  • 1
  • 1
    Why do you care what order the rows have in the permanent storage? By the way, the order that you see in the results of that select, may **not be** the order that the rows are stored. – ypercubeᵀᴹ Aug 03 '16 at 12:56
  • Ok but I don't want how they are stored internally,what I want is when ever I execute select * from cricket; I want the results to come sequentially,So that I don't want to use ORDER BY clause every time.Is there a way???? – dvr Aug 03 '16 at 13:50
  • 1
    If you don't want to use an order by clause then don't expect a specific order. – paparazzo Aug 03 '16 at 15:04
  • @Raghava `ALTER TABLE 'Cricket' ORDER BY 'sno';` will allow you to do a `select * from cricket` and get results returned ordered by sno 1,2,3,4,5,6 hope that helps as always, do a backup before making changes if you are new to working with DB. – Hector Aug 03 '16 at 17:24

2 Answers2

3

Without an ORDER BY clause, you have no guarantees as to the order the rows are presented to you, even if they 'look' ordered when you run a select. Bullet 1 of this link

https://msdn.microsoft.com/en-us/library/ms188385.aspx

Scott Hodgin - Retired
  • 23,119
  • 2
  • 21
  • 43
2

[link] http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

It suggests

ALTER TABLE 'tablename' ORDER BY 'ColumnName';
Priyanka Kariya
  • 463
  • 2
  • 10
  • 1
    That works for MyISAM, but not InnoDB. – Rick James Aug 03 '16 at 19:05
  • Just out of curiosity, can you please provide the link which explains why it does not work with InnoDB – Priyanka Kariya Aug 04 '16 at 04:59
  • 1
    Well, there are probably a hundred links that explain that InnoDB data is ordered by the table's `PRIMARY KEY`. Put another way, the PK is "clustered" with the data. With MyISAM, the PK is just another index, stored in a separate BTree, thereby leaving the data to be shuffled as with that `ALTER`. – Rick James Aug 04 '16 at 05:23