sort recordset and then save

[charlotte49er]

I need to be able to sort a table based on a field. But, (isn't there always a but?!) I need for the table contents to physically be sorted not the recordset. Is there a way to sort a recordset and then write the sorted results back into the source table? To accomplish this, will this require two tables (the source table and one where I can write the sorted results out) ? Thanks in advance.

 

[Golom]

Short answer ... NO. There is no implicit ordering of records in a RDBMS table if you don't use an ORDER BY clause. To put it another way, there is no guaranteed order in which rows will be returned from a table ... including, but not limited to ... the order in which they were written to the table.

 

[vb5prgrmr]

charlotte48er, have you read FAQ222-2244 item 15 yet? It would be a good idea for you to read the entire FAQ222-2244.

Ok, I'll give the long answer ...
[tt]
Select Into NewTable(Select Fields From OldTable Order By Field)
'...
Delete * From OldTable
'...
'perhaps a compact db here or definitly later (see below)
Select Into OldTable(Select * From NewTable)
'...
Drop NewTable
'...
'Good Idea to compact and repair here
[/tt]

You will have to look up the exact syntax in the help files.

Good Luck

 

[MichaelRed]

but even following the outline provided, the FIRST edit of hte table (ADD, Edit or Delete) has the possability of destroying the order, thus requiring the re doing of the process to maintain the order. Even considering such a process goes a Looooooooooooooooooooooog way aginst hte concept of a data base, and is totall off hte map for a relational data base. The lack of a need for such ordering is one of the basic concepts of the RDBMS.

I would suggest that you seriously re-consider the rationale for this, review some RDBMS literature, and preset the case to whatever "authourity" is asking for this.

In a serious sense, what difference can such a process make? You CANNOT see the internal organization of an RDBMS in any direct manner, so ALL views are of a RECORDSET, not actually the Tables, so just have the Recordset(s) ordered as desired. In fact, even using the procedure as outlined, there is the possability that the INTERNAL storage in some RDBMS's will not be in the sequence even at thte conclusion of the process.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places