SEO

Is there such a thing as ordered tables in sql or a simple way to emulate
it? i.e., the order in the table matters and one can insert into into the
table w.r.t to this order?

I suppose I can have a column that sorta simulates the order but the problem
is that I don't see how I can simply insert a new row without having to
modify all others. If I could simply use the inherent order of the table
then it would be much easier but then I think inserting will be very hard. I
suppose I could have a unique ID for each one and I think that will work but
not completely sure.

Not sure if its possible to insert a row inbetween rows? But if I could and
I could be sure that SQL would not modify the order then I think I could do
it easily... else I end up having to have a field that contains relative
information and I'd have to check every single row for it which is not what
I'm going to do.

Any ideas?

Thanks,
Jon

Jon Slaughter wrote:
> Is there such a thing as ordered tables in sql or a simple way to emulate
> it? i.e., the order in the table matters and one can insert into into the
> table w.r.t to this order?
>
> I suppose I can have a column that sorta simulates the order but the problem
> is that I don't see how I can simply insert a new row without having to
> modify all others. If I could simply use the inherent order of the table
> then it would be much easier but then I think inserting will be very hard. I
> suppose I could have a unique ID for each one and I think that will work but
> not completely sure.
>
> Not sure if its possible to insert a row inbetween rows? But if I could and
> I could be sure that SQL would not modify the order then I think I could do
> it easily... else I end up having to have a field that contains relative
> information and I'd have to check every single row for it which is not what
> I'm going to do.
>
> Any ideas?
>
> Thanks,
> Jon
>
>

No. By definition, tables in RDB's are unordered. That's what the
ORDER clause in the SELECT statement is for.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

"Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
news:W_2dnU0nQpb_I-zbnZ2dnUVZ_hGdnZ2d@comcast.com...
> Jon Slaughter wrote:
>> Is there such a thing as ordered tables in sql or a simple way to emulate
>> it? i.e., the order in the table matters and one can insert into into
>> the table w.r.t to this order?
>>
>> I suppose I can have a column that sorta simulates the order but the
>> problem is that I don't see how I can simply insert a new row without
>> having to modify all others. If I could simply use the inherent order of
>> the table then it would be much easier but then I think inserting will be
>> very hard. I suppose I could have a unique ID for each one and I think
>> that will work but not completely sure.
>>
>> Not sure if its possible to insert a row inbetween rows? But if I could
>> and I could be sure that SQL would not modify the order then I think I
>> could do it easily... else I end up having to have a field that contains
>> relative information and I'd have to check every single row for it which
>> is not what I'm going to do.
>>
>> Any ideas?
>>
>> Thanks,
>> Jon
>
> No. By definition, tables in RDB's are unordered. That's what the ORDER
> clause in the SELECT statement is for.
>

but how does it order it by? lexographical? I'm a little worried about how
I will set the id for each row so that when I order it it will give me the
heirarchy. I don't think I can find a sufficient solution using that unless
I can specify how it is to be ordered(without any non-portable sql code).

Jon

>>> Is there such a thing as ordered tables in sql or a simple way to emulate
>>> it? i.e., the order in the table matters and one can insert into into
>>> the table w.r.t to this order?
>>>
>>> I suppose I can have a column that sorta simulates the order but the
>>> problem is that I don't see how I can simply insert a new row without
>>> having to modify all others. If I could simply use the inherent order of
>>> the table then it would be much easier but then I think inserting will be
>>> very hard. I suppose I could have a unique ID for each one and I think
>>> that will work but not completely sure.
>>>
>>> Not sure if its possible to insert a row inbetween rows? But if I could
>>> and I could be sure that SQL would not modify the order then I think I
>>> could do it easily... else I end up having to have a field that contains
>>> relative information and I'd have to check every single row for it which
>>> is not what I'm going to do.
>>>
>>> Any ideas?
>>>
>>> Thanks,
>>> Jon
>>
>> No. By definition, tables in RDB's are unordered. That's what the ORDER
>> clause in the SELECT statement is for.
>>
>
>but how does it order it by?

If your SQL specifies ORDER BY, that's how the output is ordered.
(in the case of ties, the ordering between equal-order rows is not
defined, but if you sort on a UNIQUE key, this won't be an issue).
If your SQL does not specify ORDER BY, no matter what order you get
for the output, you can't complain about it, since it's not wrong.

>lexographical?

Malevolent. The ordering will be such that it will seem to work fine
until you demo it to the boss or a customer. See Murphy's Law.

>I'm a little worried about how
>I will set the id for each row so that when I order it it will give me the
>heirarchy. I don't think I can find a sufficient solution using that unless
>I can specify how it is to be ordered(without any non-portable sql code).

You don't have to order by numbers. You can use a string. Each
item in a hierarchy can take the ID of its parent plus one additional
character (or a fixed number of additional characters), which
determines the ordering of its sub-topics. Suppose you have this
(partial) outline for a paper:

The Internet ()

I. Viruses and Malware (1)
A. Microsoft Windows (1A)
B. Internet Explorer (1B)
II. Criminals on the Internet (2)
A. Spammers (2A)
B. Predators (2B)
1. Cops pretending to be kids (2B1)
2. Chris Hansen (2B2)
3. Cops pretending to be FBI (2B3)
C. Fraudsters (2C)
1. Phishing attacks (2C1)
2. Phalse advertising (2C2)
III. Copyright Infringement (3)
A. Stolen Music (3A)
B. Stolen Movies (3B)
C. Stolen Software (3C)

If it's an unordered hierarchy, new items can be added at the end
under the parent, for example, "Stolen Pictures" probably gets (3D).
If you insist that it has to go between "Stolen Music" and "Stolen
Movies", it gets (3B), and you only have to renumber "Stolen Movies"
and "Stolen Software".

"Gordon Burditt" <gordonb.j31n4@burditt.org> wrote in message
news:1373oig9j8ovpe4@corp.supernews.com...
>>>> Is there such a thing as ordered tables in sql or a simple way to
>>>> emulate
>>>> it? i.e., the order in the table matters and one can insert into into
>>>> the table w.r.t to this order?
>>>>
>>>> I suppose I can have a column that sorta simulates the order but the
>>>> problem is that I don't see how I can simply insert a new row without
>>>> having to modify all others. If I could simply use the inherent order
>>>> of
>>>> the table then it would be much easier but then I think inserting will
>>>> be
>>>> very hard. I suppose I could have a unique ID for each one and I think
>>>> that will work but not completely sure.
>>>>
>>>> Not sure if its possible to insert a row inbetween rows? But if I could
>>>> and I could be sure that SQL would not modify the order then I think I
>>>> could do it easily... else I end up having to have a field that
>>>> contains
>>>> relative information and I'd have to check every single row for it
>>>> which
>>>> is not what I'm going to do.
>>>>
>>>> Any ideas?
>>>>
>>>> Thanks,
>>>> Jon
>>>
>>> No. By definition, tables in RDB's are unordered. That's what the
>>> ORDER
>>> clause in the SELECT statement is for.
>>>
>>
>>but how does it order it by?
>
> If your SQL specifies ORDER BY, that's how the output is ordered.
> (in the case of ties, the ordering between equal-order rows is not
> defined, but if you sort on a UNIQUE key, this won't be an issue).
> If your SQL does not specify ORDER BY, no matter what order you get
> for the output, you can't complain about it, since it's not wrong.
>
>>lexographical?
>
> Malevolent. The ordering will be such that it will seem to work fine
> until you demo it to the boss or a customer. See Murphy's Law.
>
>>I'm a little worried about how
>>I will set the id for each row so that when I order it it will give me the
>>heirarchy. I don't think I can find a sufficient solution using that
>>unless
>>I can specify how it is to be ordered(without any non-portable sql code).
>
> You don't have to order by numbers. You can use a string. Each
> item in a hierarchy can take the ID of its parent plus one additional
> character (or a fixed number of additional characters), which
> determines the ordering of its sub-topics. Suppose you have this
> (partial) outline for a paper:
>
> The Internet ()
>
> I. Viruses and Malware (1)
> A. Microsoft Windows (1A)
> B. Internet Explorer (1B)
> II. Criminals on the Internet (2)
> A. Spammers (2A)
> B. Predators (2B)
> 1. Cops pretending to be kids (2B1)
> 2. Chris Hansen (2B2)
> 3. Cops pretending to be FBI (2B3)
> C. Fraudsters (2C)
> 1. Phishing attacks (2C1)
> 2. Phalse advertising (2C2)
> III. Copyright Infringement (3)
> A. Stolen Music (3A)
> B. Stolen Movies (3B)
> C. Stolen Software (3C)
>
> If it's an unordered hierarchy, new items can be added at the end
> under the parent, for example, "Stolen Pictures" probably gets (3D).
> If you insist that it has to go between "Stolen Music" and "Stolen
> Movies", it gets (3B), and you only have to renumber "Stolen Movies"
> and "Stolen Software".
>

I don't see how its that efficient... if I have 100's of levels with 100's
of children then using just letters can start to waste space very quickly.

if I use each char as a full byte(0..255) then how do they get compared?


I think I can use a decimal if I encode it properly to represent the level
and it should work fine with the order by.

I can't see how using a full char(0..255) and concatinating them to form an
id will be processed by order correctly and just using letters(0..25 I
suppose since its probably not case sensitive) is about 10 times more
space(and because I need a larger number of "levels" it will quickly add up
to).

Jon

Jon Slaughter wrote:
> "Gordon Burditt" <gordonb.j31n4@burditt.org> wrote in message
> news:1373oig9j8ovpe4@corp.supernews.com...
>>>>> Is there such a thing as ordered tables in sql or a simple way to
>>>>> emulate
>>>>> it? i.e., the order in the table matters and one can insert into into
>>>>> the table w.r.t to this order?
>>>>>
>>>>> I suppose I can have a column that sorta simulates the order but the
>>>>> problem is that I don't see how I can simply insert a new row without
>>>>> having to modify all others. If I could simply use the inherent order
>>>>> of
>>>>> the table then it would be much easier but then I think inserting will
>>>>> be
>>>>> very hard. I suppose I could have a unique ID for each one and I think
>>>>> that will work but not completely sure.
>>>>>
>>>>> Not sure if its possible to insert a row inbetween rows? But if I could
>>>>> and I could be sure that SQL would not modify the order then I think I
>>>>> could do it easily... else I end up having to have a field that
>>>>> contains
>>>>> relative information and I'd have to check every single row for it
>>>>> which
>>>>> is not what I'm going to do.
>>>>>
>>>>> Any ideas?
>>>>>
>>>>> Thanks,
>>>>> Jon
>>>> No. By definition, tables in RDB's are unordered. That's what the
>>>> ORDER
>>>> clause in the SELECT statement is for.
>>>>
>>> but how does it order it by?
>> If your SQL specifies ORDER BY, that's how the output is ordered.
>> (in the case of ties, the ordering between equal-order rows is not
>> defined, but if you sort on a UNIQUE key, this won't be an issue).
>> If your SQL does not specify ORDER BY, no matter what order you get
>> for the output, you can't complain about it, since it's not wrong.
>>
>>> lexographical?
>> Malevolent. The ordering will be such that it will seem to work fine
>> until you demo it to the boss or a customer. See Murphy's Law.
>>
>>> I'm a little worried about how
>>> I will set the id for each row so that when I order it it will give me the
>>> heirarchy. I don't think I can find a sufficient solution using that
>>> unless
>>> I can specify how it is to be ordered(without any non-portable sql code).
>> You don't have to order by numbers. You can use a string. Each
>> item in a hierarchy can take the ID of its parent plus one additional
>> character (or a fixed number of additional characters), which
>> determines the ordering of its sub-topics. Suppose you have this
>> (partial) outline for a paper:
>>
>> The Internet ()
>>
>> I. Viruses and Malware (1)
>> A. Microsoft Windows (1A)
>> B. Internet Explorer (1B)
>> II. Criminals on the Internet (2)
>> A. Spammers (2A)
>> B. Predators (2B)
>> 1. Cops pretending to be kids (2B1)
>> 2. Chris Hansen (2B2)
>> 3. Cops pretending to be FBI (2B3)
>> C. Fraudsters (2C)
>> 1. Phishing attacks (2C1)
>> 2. Phalse advertising (2C2)
>> III. Copyright Infringement (3)
>> A. Stolen Music (3A)
>> B. Stolen Movies (3B)
>> C. Stolen Software (3C)
>>
>> If it's an unordered hierarchy, new items can be added at the end
>> under the parent, for example, "Stolen Pictures" probably gets (3D).
>> If you insist that it has to go between "Stolen Music" and "Stolen
>> Movies", it gets (3B), and you only have to renumber "Stolen Movies"
>> and "Stolen Software".
>>
>
> I don't see how its that efficient... if I have 100's of levels with 100's
> of children then using just letters can start to waste space very quickly.
>
> if I use each char as a full byte(0..255) then how do they get compared?
>
>
> I think I can use a decimal if I encode it properly to represent the level
> and it should work fine with the order by.
>
> I can't see how using a full char(0..255) and concatinating them to form an
> id will be processed by order correctly and just using letters(0..25 I
> suppose since its probably not case sensitive) is about 10 times more
> space(and because I need a larger number of "levels" it will quickly add up
> to).
>
> Jon
>
>

The question arises - what exactly are you trying to do? 100's of
levels? 100's of children?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

"Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
news:ePCdnbphcuUXnu_bnZ2dnUVZ_oPinZ2d@comcast.com. ..
> Jon Slaughter wrote:
>> "Gordon Burditt" <gordonb.j31n4@burditt.org> wrote in message
>> news:1373oig9j8ovpe4@corp.supernews.com...
>>>>>> Is there such a thing as ordered tables in sql or a simple way to
>>>>>> emulate
>>>>>> it? i.e., the order in the table matters and one can insert into
>>>>>> into
>>>>>> the table w.r.t to this order?
>>>>>>
>>>>>> I suppose I can have a column that sorta simulates the order but the
>>>>>> problem is that I don't see how I can simply insert a new row without
>>>>>> having to modify all others. If I could simply use the inherent order
>>>>>> of
>>>>>> the table then it would be much easier but then I think inserting
>>>>>> will be
>>>>>> very hard. I suppose I could have a unique ID for each one and I
>>>>>> think
>>>>>> that will work but not completely sure.
>>>>>>
>>>>>> Not sure if its possible to insert a row inbetween rows? But if I
>>>>>> could
>>>>>> and I could be sure that SQL would not modify the order then I think
>>>>>> I
>>>>>> could do it easily... else I end up having to have a field that
>>>>>> contains
>>>>>> relative information and I'd have to check every single row for it
>>>>>> which
>>>>>> is not what I'm going to do.
>>>>>>
>>>>>> Any ideas?
>>>>>>
>>>>>> Thanks,
>>>>>> Jon
>>>>> No. By definition, tables in RDB's are unordered. That's what the
>>>>> ORDER
>>>>> clause in the SELECT statement is for.
>>>>>
>>>> but how does it order it by?
>>> If your SQL specifies ORDER BY, that's how the output is ordered.
>>> (in the case of ties, the ordering between equal-order rows is not
>>> defined, but if you sort on a UNIQUE key, this won't be an issue).
>>> If your SQL does not specify ORDER BY, no matter what order you get
>>> for the output, you can't complain about it, since it's not wrong.
>>>
>>>> lexographical?
>>> Malevolent. The ordering will be such that it will seem to work fine
>>> until you demo it to the boss or a customer. See Murphy's Law.
>>>
>>>> I'm a little worried about how
>>>> I will set the id for each row so that when I order it it will give me
>>>> the
>>>> heirarchy. I don't think I can find a sufficient solution using that
>>>> unless
>>>> I can specify how it is to be ordered(without any non-portable sql
>>>> code).
>>> You don't have to order by numbers. You can use a string. Each
>>> item in a hierarchy can take the ID of its parent plus one additional
>>> character (or a fixed number of additional characters), which
>>> determines the ordering of its sub-topics. Suppose you have this
>>> (partial) outline for a paper:
>>>
>>> The Internet ()
>>>
>>> I. Viruses and Malware (1)
>>> A. Microsoft Windows (1A)
>>> B. Internet Explorer (1B)
>>> II. Criminals on the Internet (2)
>>> A. Spammers (2A)
>>> B. Predators (2B)
>>> 1. Cops pretending to be kids (2B1)
>>> 2. Chris Hansen (2B2)
>>> 3. Cops pretending to be FBI (2B3)
>>> C. Fraudsters (2C)
>>> 1. Phishing attacks (2C1)
>>> 2. Phalse advertising (2C2)
>>> III. Copyright Infringement (3)
>>> A. Stolen Music (3A)
>>> B. Stolen Movies (3B)
>>> C. Stolen Software (3C)
>>>
>>> If it's an unordered hierarchy, new items can be added at the end
>>> under the parent, for example, "Stolen Pictures" probably gets (3D).
>>> If you insist that it has to go between "Stolen Music" and "Stolen
>>> Movies", it gets (3B), and you only have to renumber "Stolen Movies"
>>> and "Stolen Software".
>>>
>>
>> I don't see how its that efficient... if I have 100's of levels with
>> 100's of children then using just letters can start to waste space very
>> quickly.
>>
>> if I use each char as a full byte(0..255) then how do they get compared?
>>
>>
>> I think I can use a decimal if I encode it properly to represent the
>> level and it should work fine with the order by.
>>
>> I can't see how using a full char(0..255) and concatinating them to form
>> an id will be processed by order correctly and just using letters(0..25 I
>> suppose since its probably not case sensitive) is about 10 times more
>> space(and because I need a larger number of "levels" it will quickly add
>> up to).
>>
>> Jon
>
> The question arises - what exactly are you trying to do? 100's of levels?
> 100's of children?
>

100's of nodes. its about nesting or if you prefer, tree's.

On Thu, 14 Jun 2007 15:35:05 -0500, "Jon Slaughter"
<Jon_Slaughter@Hotmail.com> wrote:

>Is there such a thing as ordered tables in sql or a simple way to emulate
>it? i.e., the order in the table matters and one can insert into into the
>table w.r.t to this order?
>
>I suppose I can have a column that sorta simulates the order but the problem
>is that I don't see how I can simply insert a new row without having to
>modify all others. If I could simply use the inherent order of the table
>then it would be much easier but then I think inserting will be very hard. I
>suppose I could have a unique ID for each one and I think that will work but
>not completely sure.
>
>Not sure if its possible to insert a row inbetween rows? But if I could and
>I could be sure that SQL would not modify the order then I think I could do
>it easily... else I end up having to have a field that contains relative
>information and I'd have to check every single row for it which is not what
>I'm going to do.
>
>Any ideas?
>
>Thanks,
>Jon
>

I would do something dirty like this :
table{id, parentid, childid, order, ...}

order is a BIGINT, generated by a procedure. It starts from the row
with parentid = null, puts order = 1000. (pretending you don't plan to
have more than the max value of a BIGINT/1000 rows in your table)
You affect an order of n+1000 for the child of the row that had n as
order.

When you insert a row, you must update : the child value of its parent
and parent value of it child, and give the new row an order of
(parentorder+childorder)/2. That's done with a procedure but it's not
too heavy and has to modify only two rows.
If it is not possible to get a proper order (neworder=parentorder OR
neworder = childorder), call the procedure that reaffects all the
orders (which will be done only when you have inserted 1000 rows
between two original rows).

On Jun 15, 7:18 am, subtenante <zzsubtenant...@gmail.com> wrote:
> On Thu, 14 Jun 2007 15:35:05 -0500, "Jon Slaughter"
>
>
>
> <Jon_Slaugh...@Hotmail.com> wrote:
> >Is there such a thing as ordered tables in sql or a simple way to emulate
> >it? i.e., the order in the table matters and one can insert into into the
> >table w.r.t to this order?
>
> >I suppose I can have a column that sorta simulates the order but the problem
> >is that I don't see how I can simply insert a new row without having to
> >modify all others. If I could simply use the inherent order of the table
> >then it would be much easier but then I think inserting will be very hard. I
> >suppose I could have a unique ID for each one and I think that will work but
> >not completely sure.
>
> >Not sure if its possible to insert a row inbetween rows? But if I could and
> >I could be sure that SQL would not modify the order then I think I could do
> >it easily... else I end up having to have a field that contains relative
> >information and I'd have to check every single row for it which is not what
> >I'm going to do.
>
> >Any ideas?
>
> >Thanks,
> >Jon
>
> I would do something dirty like this :
> table{id, parentid, childid, order, ...}
>
> order is a BIGINT, generated by a procedure. It starts from the row
> with parentid = null, puts order = 1000. (pretending you don't plan to
> have more than the max value of a BIGINT/1000 rows in your table)
> You affect an order of n+1000 for the child of the row that had n as
> order.
>
> When you insert a row, you must update : the child value of its parent
> and parent value of it child, and give the new row an order of
> (parentorder+childorder)/2. That's done with a procedure but it's not
> too heavy and has to modify only two rows.
> If it is not possible to get a proper order (neworder=parentorder OR
> neworder = childorder), call the procedure that reaffects all the
> orders (which will be done only when you have inserted 1000 rows
> between two original rows).


Perhaps you should take a look at nested sets:

http://dev.mysql.com/tech-resources/...ical-data.html

There's a good article at sitepoint too

On Fri, 15 Jun 2007 00:41:47 -0700, strawberry <zac.carey@gmail.com>
wrote:

>Perhaps you should take a look at nested sets:
>
>http://dev.mysql.com/tech-resources/...ical-data.html
>
>There's a good article at sitepoint too

This is a good thing for trees but for linked list, i'm not sure it is
of great help, it would result in the solution Jon was running away
from (updating in mean half of the rows for each insert/delete).