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?

Improve this question
4
  • 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ᵀᴹ
    Commented 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
    Commented 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
    Commented 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
    Commented Aug 3, 2016 at 17:24

2 Answers 2

Reset to default
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

Improve this answer
1
  • 2
    +1 The link applies only to SQL Server, but the answer applies to any SQL.
    – Leigh Riffel
    Commented Aug 3, 2016 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';
Improve this answer
3
  • 1
    That works for MyISAM, but not InnoDB.
    – Rick James
    Commented 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
    – Priyanka Kariya
    Commented 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
    Commented Aug 4, 2016 at 5:23

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