Alistair,
While the concept of pre-sorted tables seem appealing users
should be aware that the SQL standards states explicitly that
programming should not assume that a SELECT statements without
an ORDER BY clause will consistently return data in a specific
sequence. While current implementations may do so, it is not
wise, in view of SQL standards, to assume that this will occur
in a future release. When you deal with multi-table selects the
syntax analyzer and the optimizer along with PRIMARY KEYS,
SECONDARY KEYS, and indexes may return data in a different
order.
It should be a "best practice" that when you require data to be
returned in a particular sequence you include and ORDER BY
clause. As RBase includes more advanced features of the SQL
1999 or SQL 2003 standard ignoring such standards could have
unintended consequences.
Jim Bentley
--- Alastair Burr <[EMAIL PROTECTED]> wrote:
> 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
>
Jim Bentley
American Celiac Society
[EMAIL PROTECTED]
tel: 1-504-737-3293
____________________________________________________________________________________
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs
