Changing default sorting behavior of mysql

Question

I have a table in my database.

+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| rollno | int(11)     | NO   |     | NULL    |                |
| name   | varchar(20) | NO   |     | NULL    |                |
| marks  | int(11)     | NO   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

By default if I query

select * from students;

Shows result sorted by id INT (auto-increment).

+----+--------+------------+-------+
| id | rollno | name       | marks |
+----+--------+------------+-------+
|  1 |     65 | John Doe   |    89 |
|  2 |     62 | John Skeet |    76 |
|  3 |     33 | Mike Ross  |    78 |
+----+--------+------------+-------+
3 rows in set (0.00 sec)

I want to change default sorting behaviour and make rollno the default sorting field, how do I do this?

Answer 1

There is no default sort order!

The DB returns the data in the fastest way possible. If this happen to be the order in which it is stored or a key is defined then this is up to the system. You can't rely on that.

Think about it: Why would the DB use performace to order something by default if you don't need it ordered. DBs are optimised for speed.

If you want it being ordered then you have to specify that in an order by clause.

Answer 2

Run this

ALTER TABLE students ORDER BY rollno ASC;