Whilst agreeing with others who have indicated that the SQL ORDER BY clause should be used in the vast majority of cases and especially in multi-user environments there are exceptions where having tables pre-sorted can be useful.
For instance, in my case, I have a table of music tracks with an idnum, tracknumber, title, etc. I like to have this table pre-sorted by tracknumber within idnumber. I can think of no scenario where I might want the tracknumbers jumbled up. I might want the title sorted alphabetically or by playing time but never "unsorted". Generally, I like to have my tables in order of their idnum - usually the primary key. I find that when looking for errors (mine) or problems it can be easier to see where there might be a point at issue: missing idnums, perhaps. With pre-sorted tables when using the Data Browser there is less need to have to sort the display before it can be useful. Obviously not always but often enough to make it worthwhile - particularly with the more static data. I also like to re-build my databases at the turn of each year. As briefly discussed here earlier this year I do this by unloading the tables in a particular sequence (of tables) so that they can be reloaded to a new database without conflicts arising due to missing keys from tables that should have been loaded first. Since I have to unload the data anyway I do so with the ORDER BY clause that best suits each table for my needs. This, effectively, kills two birds with one stone: I get a clean database and its tables are pre-sorted. As a single user there is sometimes a chance that I know what I have done so I am aware that the tables are only partially sorted where data is added regularly. My application takes care to sort as applicable. However, when at the R:> or in the Data Browser my data is often presented to me without having to sort it. Also, it so happens, that much of the data will be added in the "correct" order in any case via the application. (For some reason which I have never really understood, I tend to add the tracks, for example, in the order that they appear - I guess that it is just easier than thinking about which one I should enter next <g>. I usually only get caught out when artists think it fun not to number tracks and print the titles in some obscure order!) That said, and even though the process is very quick, I would not like to have to rely on re-building the database regularly in order to avoid having to use the ORDER BY clause. It's a question of horses for courses: I can make use of pre-sorted tables in many instances and I lose nothing by having to sort those where there is a need. Regards, Alastair. ----- Original Message ----- From: Fred C Kopp To: RBASE-L Mailing List Sent: Wednesday, February 06, 2008 11:45 PM Subject: [RBASE-L] - Build PK from command file I like to sort tables frequently (LastName,FirstName,etc.) so that we don't always have to add an ORDER BY clause. Just lazy, I guess. This is probably not great practice but... From a command file I can RENAME a table to, say, TEMP (I learned the hard way to use NOCHECK to avoid messing up forms and reports based on that table). Then I can PROJECT <original table name> from TEMP USING * ORDER BY LastName FirstName. Then I can DROP TABLE TEMP. NOW, how do I build a PK for ColName from the command file? ALTER TABLE is part of it BUT it wants me to declare UNIQUE VALUE for ColName before it will build the key. I know it's in the syntax somewhere but I just can't find it. I think it's more than one command. This doesn't seem all that unusual a thing to do. Could someone please help me with the syntax? Or, is there a better way to get the same result? (There usually is). I'm also interested in how to build FK and index from a command file. I realize that RELOAD or PACK would be a good thing to do after swelling up the database. Thanks, Fred Fred C. Kopp Authorized R:Base Developer 19 Teri Lane Washington, PA 15301 P 724-222-7376 F 724-222-7376 C 724-413-5534 E [EMAIL PROTECTED] ------------------------------------------------------------------------------ No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.516 / Virus Database: 269.19.20/1261 - Release Date: 05/02/2008 20:57
