Professional Web Applications Themes

ordered tables - MySQL

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

  1. #1

    Default ordered tables

    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 Guest

  2. #2

    Default Re: ordered tables

    Jon Slaughter wrote: 

    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.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: ordered tables


    "Jerry Stuckle" <net> wrote in message
    news:com... 
    >
    > No. By definition, tables in RDB's are unordered. That's what the ORDER
    > clause in the SELECT statement is for.
    >[/ref]

    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


    Jon Guest

  4. #4

    Default Re: ordered tables

    >>> Is there such a thing as ordered tables in sql or a simple way to emulate 
    >>
    >> No. By definition, tables in RDB's are unordered. That's what the ORDER
    >> clause in the SELECT statement is for.
    >>[/ref]
    >
    >but how does it order it by?[/ref]

    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.
     

    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.
     

    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 Guest

  5. #5

    Default Re: ordered tables


    "Gordon Burditt" <org> wrote in message
    news:supernews.com... 
    >>
    >>but how does it order it by?[/ref]
    >
    > 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.

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

    >
    > 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".
    >[/ref]

    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 Guest

  6. #6

    Default Re: ordered tables

    Jon Slaughter wrote: 
    >> 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.
    >> 
    >> 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.
    >> 
    >> 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".
    >>[/ref]
    >
    > 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
    >
    >[/ref]

    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.
    net
    ==================
    Jerry Guest

  7. #7

    Default Re: ordered tables


    "Jerry Stuckle" <net> wrote in message
    news:com... 
    >>
    >> 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[/ref]
    >
    > The question arises - what exactly are you trying to do? 100's of levels?
    > 100's of children?
    >[/ref]

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


    Jon Guest

  8. #8

    Default Re: ordered tables

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

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

  9. #9

    Default Re: ordered tables

    On Jun 15, 7:18 am, subtenante <com> wrote: 




    >
    > 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).[/ref]


    Perhaps you should take a look at nested sets:

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

    There's a good article at sitepoint too

    strawberry Guest

  10. #10

    Default Re: ordered tables

    On Fri, 15 Jun 2007 00:41:47 -0700, strawberry <com>
    wrote:
     

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

  11. #11

    Default Re: ordered tables

    subtenante wrote: 
    >
    > 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).[/ref]

    But Jon has said:
    "100's of nodes. its (SIC) about nesting or if you prefer, tree's."


    Paul Guest

  12. #12

    Default Re: ordered tables

    On Fri, 15 Jun 2007 14:37:40 +0100, "Paul Lautman"
    <com> wrote:
     
    >
    >But Jon has said:
    >"100's of nodes. its (SIC) about nesting or if you prefer, tree's."[/ref]

    True, but if you want to order a table, you have more precisely a
    linked list shape (which is a tree, except it has only one branch).
    And in that case, using the nested sets pattern makes it quite absurd
    : each set contains only one set containing one set containing one
    set... therefore the left column lists your elements in one sense, and
    the right column lists them backwards. So it provides no advantage :
    for each insert/delete, you have to update two values in each row of
    half of your columns, instead of updating only one with the initial
    shape Jon had in the first place (which is only the equivalent of the
    left column in nested sets).
    subtenante Guest

  13. #13

    Default Re: ordered tables

    On Fri, 15 Jun 2007 22:47:28 +0800, subtenante
    <com> wrote:

    Sorry got mixed up ! I correct :

    So it provides no advantage :
    for each insert/delete, you have to update two values in half of your
    ROWS (in mean)
    subtenante Guest

  14. #14

    Default Re: ordered tables


    "Paul Lautman" <com> wrote in message
    news:individual.net... 
    >>
    >> 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).[/ref]
    >
    > But Jon has said:
    > "100's of nodes. its (SIC) about nesting or if you prefer, tree's."
    >[/ref]

    Yes I did.. but nesting can be many things and there are many types of
    tree's. subtenante was right that I want to avoid having to update all the
    rows for each insert. Since there are no deletes I think a linked list like
    method is probably the best but unfortunately I really need a multilinked
    list...

    Since there can only be one parent and multiple children I think I can use a
    variant of the linked list version he suggested. Just keep all the
    children's id's in the parent and update only the parent when a new child is
    added. Since all the children are specificed in the parent I should easily
    be able to traverse through the tree in the manner I wanted.

    Here the order by sql won't matter because the order is contained in the
    parents field that contains all the children's id's. So this is another
    plus.

    So far this method seems to be the best and does what I want.


    Jon Guest

  15. #15

    Default Re: ordered tables


    "subtenante" <com> wrote in message
    news:com... 
    >
    > 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).[/ref]

    Ok, I think I will use a variant of this. childid will actually be a
    contatenation of all the child id's so that I can have multipl children per
    parent. I don't think this significantly changes anything you said though.
    All I have to do is update the parent by appending the new child id to the
    childid's in the parent... I don't even need a procedure.

    make sense?

    Thanks,
    Jon


    Jon Guest

  16. #16

    Default Re: ordered tables

    On 15 jun, 14:21, "Jon Slaughter" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]



    >
    > Ok, I think I will use a variant of this. childid will actually be a
    > contatenation of all the child id's so that I can have multipl children per
    > parent. I don't think this significantly changes anything you said though.
    > All I have to do is update the parent by appending the new child id to the
    > childid's in the parent... I don't even need a procedure.
    >
    > make sense?
    >
    > Thanks,
    > Jon[/ref]

    I've done that sort of ordering. I like tu use mysql to store a kind
    of scripting procedures that must be processed in order.
    Such as "sum of sales of 2 accounts A and B in account C, delete
    Account A, Delete account B.... etc"
    As you can see, this are 3 rows in my database.. that a program reads
    sequentialy and executes a procedure based on what the record says.
    It obvious that if it's not processed in order.. the result is
    different or just ends up crashing.
    So, I looked back to the old days of BASIC.. yes.. the famous Biginers
    All-purpose Symbolic Instruction Code.(not VB)
    On that days, programs used to have line numbers... such as
    10 Let a="Hello world"
    20 Print a
    30 goto 20

    as you can see, lines where numbered 10 by 10.. so if you needed to
    insert lines in the middle, you just add line 15 Let a= a +"!!!!!" to
    end up with
    10 Let a="Hello world"
    15 Let a= a +"!!!!!"
    20 Print a
    30 goto 20

    Well, it was quite easy to end up without space.. so just type
    RENUMBER (was it renumber?).. and presto!
    10 Let a="Hello world"
    20 Let a= a +"!!!!!"
    30 Print a
    40 goto 30

    So, I decided to follow the idea.
    All I needed was a renumbering procedure... which I won't type now
    because It's 3 in the morning and I'm quite exhausted.. and btw,
    you'll get the idea.
    what you need is a table with a LINE NUMBER COLUMN.
    The increment on the line number, I like it to be a power of 2, as
    2,4,8 16,32, 64 and so on has always an integer in it's middle where
    to insert. Use a trigger to update that column, not an autoincrement.
    Well, let's say that you added stuff to the database, it looks neat..
    but wait!.. I need to insert a few row in the middle.. well. depending
    on the increment you used, you got space...
    But hay.. what if I ran out of space?
    Well, renumber the rows.
    Renumbering is done with a procedure, that counts the rows, multiplies
    it by the increment... (let's say 32) to get the largest line number,
    and using a cursor with a select ordered by linenumber DECRementing (<-
    VERY IMPORTANT!!!) . I update the last row with that value, subtract
    32, fetch, update, subtract, fetch... and so on.
    Result, linenumber renumbered and you got your space back.
    It's important to do it backwards because if you do it forewards, you
    might be stepping over other record's line number and during the
    procedure, you end up with 2 records with linenumber, let's say, 64.
    So when we get to line 64, what record need's it's line updated?... If
    you got the idea, great, if you don't.. trust me.. don't re-invent the
    wheel.

    Hope it helped you out.
    Carlos Troncoso Phillips


    Carlos Guest

  17. #17

    Default Re: ordered tables

    On Fri, 15 Jun 2007 18:21:00 GMT, "Jon Slaughter"
    <com> wrote:
     

    Ok, sorry i didn't understand you had really a tree structure, I
    thought from your first post it was a total order, so Captain was
    right in his remark higher.

    Anyways i think having a concatenated childid column is a bad id. You
    can have all the children with a
    SELECT * FROM table WHERE parentid=...
    The reason I put it in the first place was only convenience, but now
    you have the inconvenient of needing to p your childid column. And
    personnally I would say : don't pretend you wil not delete. Eventually
    you will (because of mistakes, or anything). So keep your things
    normalized as much as you can : you may have a hard time removing
    values inside of concatenated fields (I don't mind doing a dirty thing
    from time to time, assuming certain things that are really unlikely to
    be overruled, but this is *really* dirty). But i don't know everything
    of the case, so if you have really good reasons to do so, do so. But
    find out your good reasons before.
    subtenante Guest

  18. #18

    Default Re: ordered tables

    On Sat, 16 Jun 2007 07:33:06 -0000, Carlos Troncoso
    <com> wrote:
     

    This power-of-2 thing is very smart !
    subtenante Guest

Similar Threads

  1. position in ordered results
    By Iain in forum MySQL
    Replies: 3
    Last Post: May 25th, 08:51 AM
  2. Ordered list in cfoutput?
    By SquiSh1717 in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: July 10th, 12:06 PM
  3. Randomly Ordered Recordset
    By Brandon Taylor in forum Dreamweaver AppDev
    Replies: 3
    Last Post: February 24th, 04:59 PM
  4. Replies: 3
    Last Post: December 9th, 03:34 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139