How: Persistant record order -- sql server mseq

Gary K
11/7/2004 8:17:02 PM

Does anyone know a (relativly) easy way of keeping records in their created
order? (or a specific order, and maintaining that order through
additions/deletions/changes)
I'm interested in a general method (ie, works for everything), but I'll use
my specific example as explaination.
The ExamText table holds the body text for a page, related to the text are
the ExamQuestions, which hold the question text (& indicates single/multiple
answer choices), and is related to the ExamAnswers table, which holds the
answer text (& indicates if it is a right answer).
What we have found is that the questions/answers are not retrieved in the
order they were created, and this does tend to confuse a few people (some of
the Q/A was ordered for a specific reason). What we would like to do is
retrieve them in their created order (or at least a specific order).
I know I can create an extra order column and sort by that in the select
statements, but they will also be high-traffic tables, with a lot of
creation/editing of records, and I would prefer not to create a lot of
'ordering' code to handle the re-ordering of items.
Does anyone know of an easier way? or have I hit the nail on the head the

Hugo Kornelis
11/8/2004 9:35:34 AM

[quoted text, click to view]

Hi Gary,

Yes, you hit the nail on the head. Tables in a relational database are
unordered by definition - the only way to force a desired ordering of a
result set is to use the ORDER BY clause, and that typically requires that
there is some data element in the table(s) that you can order on.

You could minimize the extra work load by adding a date_inserted (or maybe
even moment_inserted) column to the relevant tables. The easiest way to
set this column equal to CURRENT_TIMESTAMP (either trimmed to date only or
complete) when a row is inserted is to use a default; assuming you don't
use INSERT without column list and SELECT * (which you really should not
do anyway), you won't have to change your insert, update or delete
statements.

To retrieve the rows in the order inserted, you add ORDER BY date_inserted
at the end of the query.

Best, Hugo
--

sql server mseq : How: Persistant record order