Sorting a db table

[mrdj1002]

hi guys
I wanted to know what is the DDL command for sorting a table in a db refefering to one or more fields i want to use the SQL statement in such way:

db.execute SQLstatement

can any body help?
thnks alot

[techyspecy]

Originally posted by mrdj1002
hi guys
I wanted to know what is the DDL command for sorting a table in a db refefering to one or more fields i want to use the SQL statement in such way:

db.execute SQLstatement

can any body help?
thnks alot

Order by fieldA, fieldB and so on...

[trutta]

There's also the Group By statement if that's useful to you.

[mrdj1002]

Originally posted by techyspecy
Order by fieldA, fieldB and so on...

I C but
what is the ddl statement? for example in query we have:

Selecte fieldA,fieledB from Table order by fieldA,fieldB

but it's just a query of this table and i want to sort the actual table in the database so i need a DDL command and use "order by" at the end. not a DML command like "select".
can u give me the DDL command please?
thank u

[trutta]

I don't see any such command in the list of DDL commands in The Complete Reference SQL. It would seem to be an inefficient task in any case - sorting the actual table. You can create indexes, views, etc.

[mrdj1002]

Originally posted by trutta
I don't see any such command in the list of DDL commands in The Complete Reference SQL. It would seem to be an inefficient task in any case - sorting the actual table. You can create indexes, views, etc.

u r right but my problem is that my app is cooprating with a second app which uses a terible db architaecter. i want to sort its table in every appending record coz i guess the app develeopers had counted on the tables being sorted.
thats why i'm trying to sort the actual table.
anywat thanks alot

[trutta]

Well, you could copy the data into a new table, sorting along the way.

[goatsucker]

I don't think there is a command specifically to do this. One of the principles of Relational databases is that the physical structure of the data in the database is hidden from outside the database itself. As such these kind of operations are left out of the DDL language.

Having said that, something similar to trutta's suggestion might work. I would try removing all the rows from your table, and re-insert them in the order you want. Of course new INSERTs will not respect this order, and I don't think there is any guarantee they will remain in that order even without new records being inserted.

A better solution, in which the order would be preserved would be to put a clustered index on the field(s) you want the table ordered by. Of course this won't work on simple dbms's like Acces which don't have clustered indexes, but this will work on SQL Server and sybase, and I would be surprised if it didn't work on Oracle.

[randem]

mrdj1002,

I seem to be lost here....


Indexes are for keeping the appearance of sorted data.

SQL Statements with the Order By Clause will retrieve data in a sorted fashion.


Why would you attempt to physically sort a table?????

BTW there is no such command or ever will be!

[mrdj1002]

Originally posted by randem
mrdj1002,

I seem to be lost here....


Indexes are for keeping the appearance of sorted data.

SQL Statements with the Order By Clause will retrieve data in a sorted fashion.


Why would you attempt to physically sort a table?????

BTW there is no such command or ever will be!

well it seems i have lack of db knowledg. I mean sorting the indexes by sorting table. u know when i open a table into a recordset and move on it record by record for example when i use such codes:

set rs=db.openrecordset("TableName")

rs.movefirst
rs.movenext

now i want to sort indexes so that when move to first record i can get the minimum (or max) throu field which i have chosen to sort the table according to.
so i guess in some cases it is neccessery to have indexs sorted regarding to a spetial field.

[randem]

mrdj1002,

OK, we have a starting point.

First if you want your recordset sorted all you need to do is include in your SQL Select statement the Order By Clause

Select * from [TableName] Order By Field1, Field2...

or

Select * from [TableName] where Field = 'some value' Order By Field1 etc...


This will return your whole table or selected records to you in a sorted recordset.


Indexes in a database are used for speed in searching through the table for your data. The are also used to limit the types of data that can be enteren into your table. ie... Unique types of records (unique indexed fields). The database uses the indexes automatically all you need to do is set them up (correctly helps alot).

In your program you will not have be bothered by the indexes. Your main concern would be the sort order (if needed).

The database controls the order of the data that is entered into it and you do not have to worry about that, Since you can get the data returned to you anyway you want. The order of the fields in your recordset (Columns), the sort order or only one field and sometimes the number of records (Rows).


Hope this clear up a little. Database programming is very different than the use of flat files. Many people program with databases but not many program well. A lot of thought has to go into the design to maximize the way it can be utilized.

[techgnome]

If you want the data physically sorted.... you can use a clustered index, if the table doesn't already have one. If it does, then you are outta luck.