Forcing a sequence in a table

[Victor Boris Arnold]

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.)

 

[Dwayne Shrum]

Not really that clever of a mechanism.

One just needs to have something they are ordering by that
already exists or put some column in the table to help
with the ordering.

Without an index or something to "Order by", there is no
guarantee of the ordering of a query from what I
understand.

 

[Victor Boris Arnold]

Yeah, If figured that I'd probably have to have a Sequence
Number column that is not visible to the user. I'd have
some way for the user, however, to specify where exactly
the new data should be inserted. Then, behind the scenes,
the Sequence Number column would have to be repopulated
for all those rows that come after the insertion point. I
was just wondering if anyone had actually done something
like this, or had a better scheme.

 

[Victor Boris Arnold]

I don't think any of those suggestions will work for my
application. If row three was added last, but needs to be
listed before row two, none of these values will do the
trick.

-----Original Message-----
There is no point in worying about the physical

arrangement of data. Add a column that is timestamp,
autonumber, or some other scheme like max()+1 when
inserting new rows. Then use an Order By clause to sort
the result.

 

[Dwayne Shrum]

Ok, so you want to have some "behind the scene" ording
takig place where the user just identifies a row and says
for it to be placed higher or lower in the list?

If so, just add a column for ordering. Count the current
records that make up the set and order by the new hidden
column. Have some controls added to move a row higher or
lower where you have the ordering fields swapped between
it and the next or previous record in the set. Refresh the
view after each click of the up/down controls.

Have another bit of code deal with counting like records
and adding 1 to the total for newly inserted records (like
a autonumber - performed more manually on sets as you are
grouping them).

This is kind of how things like DNS ordering is being
presented to the operating system users for Windows under
TCP/IP advanced properties.

 

[John Nurick]

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.

 

[Adrian Jansen]

Very neat ! And doesnt involve renumbering part of the data after insertion.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[John Nurick]

Very neat ! And doesnt involve renumbering part of the data after insertion.

After enough insertions between previous insertions - a few hundred
thousand or million, at a guess - it would hit the limit of resolution
of the Double datatype. Then it would be necessary to re-number.

 

[Adrian Jansen]

As long as you use Double, the precision is around 14 decimal digits, so you
could expect problems up around 10^13 insertions.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[John Nurick]

As long as you use Double, the precision is around 14 decimal digits, so you
could expect problems up around 10^13 insertions.

What about the worst case, where each new insertion is adjacent to the
most recent insertion, so there's a geometrical progression?

 

[Adrian Jansen]

Yuck, yes. If you use your average idea, I guess you can expect around 40
adjacent insertions before it blows up. I still think its a neat trick.
--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control