Re: Forcing a sequence in a table

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: John Nurick (j.mapSoN.nurick_at_dial.pipex.com)
Date: 06/01/04 

Date: Tue, 01 Jun 2004 22:09:13 +0100

Hi Victor,

One of the consequences of a table having no inherent order is that
there is no guarantee that the records will appear in the same order
each time you look at them.

This means that you cannot meaningfully speak of inserting a new row
"between" two existing ones unless there is some key, a field or
combination of fields, on which the records are already sorted.

If you need to create one, consider using a Double field, I'll call it
SortOrder. Add it to the table and use some code to insert values in
order 1.00, 2.00, 3.00...

Then when inserting a record "between" two others, get the SortOrder
values from those two and give the new record a SortOrder that is the
average of the two.

On Tue, 1 Jun 2004 10:09:10 -0700, "Victor Boris Arnold"
<anonymous@discussions.microsoft.com> wrote:

>I understand that the rows in a table have no inherent
>sequence, but I'm wondering if anyone has come up with a
>successful scheme to do just that.
>
>Let's say I have two rows in my table and want to insert
>another row, then be able to present all the rows as if
>the new row was inserted in between the two existing rows.
>(There would not be a field that is entered by the user
>that would allow sorting these rows in the desired
>sequence.) 

--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.

Relevant Pages

  • Re: Row Order
    ... but that doesn't guarantee they will be located in the ... IDENTITY property, then can I assume that the IDENTITY property will ... Remember this is a TEMP table I'm ... >> I'm inserting rows into a temp table via a SELECT statement that has an ...
    (microsoft.public.sqlserver.programming)
  • Re: Forcing a sequence in a table
    ... J & K MicroSystems ... "John Nurick" wrote in message ... > Then when inserting a record "between" two others, get the SortOrder ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Which row inserted first
    ... Serial column is added to know that which row is inserted first. ... inserting values into the serial column it should b like this ... Without specifying ORDER BY clause you cannot guarantee the order ...
    (microsoft.public.sqlserver.server)
  • Re: Getting the key ID from a newly inserted record
    ... then you can't guarantee that another user ... >> won't have inserted a record after your insert but before you reading ... > I think @@IDENTITY is connection-specific, so that users inserting ... > records on other connections won't affect the value returned for the ...
    (microsoft.public.access.modulesdaovba)
  • Re: timestamps as part of a primary or foreign key.
    ... no two threads will every be inserting at the same time, ... and note that the DATETIME value is accurate only ... guarantee that your insert rate was serialized to ... two/second. ...
    (comp.databases.sybase)