Sorting a Table

greenspun.com : LUSENET : DBAzine : One Thread

Hi, I've created a table with six columns, one of which is of "number" data type (let's call it col_1). There are about 2 million records in the table. However, when I viewed the records they are in random order with respect to col_1. How can I recreate the table, so that they are in ascending order with respect to col_1.

Thanks in advance.

-- John Mayol (mayolj@hotmail.com), July 16, 2001

Answers

Well, the beauty of the relational model for databases is that data storage is forced to be in any particular order. Whenever you issue a select against the table, simply use the "order by" clause to force the results into the sorted order, for example:

select * from table order by col_1

This will force the results into ascending order by col_1.

If you are using DB2 or SQL Server you can create a clustering index on the col_1 column and reorganize the data to re-establish clustering. Clustering will cause the DBMS to try to physically store the data contiguously on disk by the clustering key. Then, whenever you select all of the rows it should come back in order by the clustering column (but this is not really guaranteed without the "order by" clause).

-- Craig S. Mullins (Craig_Mullins@BMC.com), August 13, 2001.


Moderation questions? read the FAQ