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?

  • 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. Aug 3, 2016 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 3, 2016 at 13:50
  • 1
    If you don't want to use an order by clause then don't expect a specific order.
    – paparazzo
    Aug 3, 2016 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 3, 2016 at 17:24

2 Answers 2


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


  • 2
    +1 The link applies only to SQL Server, but the answer applies to any SQL. Aug 3, 2016 at 14:50

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

It suggests

ALTER TABLE 'tablename' ORDER BY 'ColumnName';
  • 1
    That works for MyISAM, but not InnoDB.
    – Rick James
    Aug 3, 2016 at 19:05
  • Just out of curiosity, can you please provide the link which explains why it does not work with InnoDB Aug 4, 2016 at 4: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 4, 2016 at 5:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.