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ᵀᴹ
  • 97,895
  • 13
  • 214
  • 305
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,854
  • 2
  • 26
  • 48
2

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

It suggests

ALTER TABLE 'tablename' ORDER BY 'ColumnName';
Priyanka Kariya
  • 473
  • 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