1

This question already has an answer here:

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?

marked as duplicate by Paul White Aug 4 '16 at 4:11

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 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 3 '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???? – Raghava Aug 3 '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 3 '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 3 '16 at 17:24
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

  • 2
    +1 The link applies only to SQL Server, but the answer applies to any SQL. – Leigh Riffel Aug 3 '16 at 14:50
2

[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 '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 4 '16 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 '16 at 5:23

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