Re: How: Persistant record order
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 11/08/04
- Next message: Ingar Eide: "SQL-query, optimizing"
- Previous message: Gary K: "How: Persistant record order"
- In reply to: Gary K: "How: Persistant record order"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 08 Nov 2004 09:35:34 +0100
On Sun, 7 Nov 2004 20:17:02 -0800, Gary K wrote:
>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
>first time (and therefore I'm stuck with the extra work load)?
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
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: Ingar Eide: "SQL-query, optimizing"
- Previous message: Gary K: "How: Persistant record order"
- In reply to: Gary K: "How: Persistant record order"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
