How to sort the MySql Database?

Question

I have stored various records in the MySql database "orkut". Now I want to sort that database through a java program. I have connected to the database through the jdbc driver. Now I want to sort that database in decreasing order of the field "number" of type "int" but don't know the commands. I have "con" reference variable which denotes the connection to the MySql database.

One more thing, there is a field "sr_no" that denotes the serial no. of the record and it is not the primary key.

I want that this field won't change after sorting the database as the serial no. should not change on changing the order of the records.

I want this sorting permanently stored on the same database. I don't want sorted ResultSet. I want sorted database.

Answer 1

Don't try to sort this through Java--you'll kill yourself trying. SQL has an order by clause that does exactly this. Here's the SQL:

select
    number,
    sr_no
from
    tbl
order by
    number desc

Also note that you cannot have a permanently sorted database. The way that the data is stored does not lend itself to being stored in whatever order you choose. You should never count on the order of a database to be the same, unless you use an order by in your query.

Answer 2

as eric told you can't have a permanently sorted database. But if you want to execute this query on a large dataset very frequently then you can do indexing supported by various database.

It will speedup your searching and sorting for a particular key.