sifting sequencing problem

  • hi,

    DROP TABLE MARBLE

    CREATE TABLE [dbo].MARBLE(

    [marbleid] [int] IDENTITY(1,1) NOT NULL,

    [name] [varchar](10) NULL,

    gluefromid int null,

    gluetoid int null,

    seqnumber int null

    CONSTRAINT [PK_marble] PRIMARY KEY CLUSTERED

    (

    Marbleid ASC

    )

    )

    go

    insert into MARBLE values('01',1,2,1)

    insert into MARBLE values('02',2,3,2)

    insert into MARBLE values('03',8,9,3)

    insert into MARBLE values('04',null,null,4)

    insert into MARBLE values('05',null,null,5)

    insert into MARBLE values('06',null,null,6)

    insert into MARBLE values('07',null,null,7)

    insert into MARBLE values('08',9,10,8)

    insert into MARBLE values('09',null,null,9)

    insert into MARBLE values('10',null,null,10)

    drop table [glue]

    CREATE TABLE [dbo].[glue](

    [glueid] [int] IDENTITY(1,1) NOT NULL,

    [sequenceno] [int] NULL,

    [name] [varchar](10) NULL,

    CONSTRAINT [PK_glue] PRIMARY KEY CLUSTERED

    (

    [glueid] ASC

    )

    )

    insert into glue values(1,'01')

    insert into glue values(2,'02')

    insert into glue values(3,'03')

    insert into glue values(4,'04')

    insert into glue values(5,'05')

    insert into glue values(6,'06')

    insert into glue values(7,'07')

    insert into glue values(8,'08')

    insert into glue values(9,'09')

    insert into glue values(10,'10')

    -- glue is used to attach marbles to make isotopes, fortunately they are in one strait line so one marble will be attached to different marble.

    -- i have to write a stored proc to sequence(shift) the marble as soon as marble is attached to two glues. any marble can be attached to any marble but using glue, any glue can be attached to any marble

    , yes ther can be situation , that two glues are used to attach a marble from two sides in database, then later after a month they can attach other marble to theses glues to make a line. so there can be small chunks.

    -- what we have to remember is we have to attach slowly all marbles with glues to make a strait line but order of attachment can be random.

    --ex third marble is attached to 8 and 9 glue , so when it is passed to the stored proc it should put it self between 07 and 08 marble (by putting sequenceno of 03 marble to 8 and sifiting

    all marbles below it.)

    yours sincerely

  • Good job with the DDL and the sample data, can you complete this by posting the desired results?

    😎

  • --All the marbles should be in order, Relating to the new attached glues.

  • rajemessage 14195 (11/11/2016)


    --All the marbles should be in order, Relating to the new attached glues.

    You've told us that you have to write a proc and I'm guessing that you want some help with that, but what is your question, exactly?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • rajemessage 14195 (11/11/2016)


    --All the marbles should be in order, Relating to the new attached glues.

    That doesn't help us.

    What we need is a sample or 3 of the data in different possible orders that would work exactly like you desire.

    In other words, if you were looking at the results in SSMS Grid / Text view, how do you want your numbers to look and in what column order?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Looking at these tables, I'm a little confused. The sequence number in table Marble seems to be pre-identified and in strict order. But your requirements seem to indicate that the sequence can change. So you seem to be missing some requirements. Here are my questions.

    1) Under what circumstances will changes be made to the glues on the marbles and the sequencing?

    2) Is this homework?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • following is the step:

    initially some sequence is given to marble table, at that time glueid in marble table are kept null.

    then one by one glue is attached to marble to make a line at that time glueid is entered in marble table.

    when ever glueid is entered in marble table, we are entering both glueid's gluefromid and gluetoid at a time or both will be null

    my requirment is to change the sequenceno according to the attached glue which can be attached to any marble.

    but one thing is finel that only two marble can use a glueid.

    so that when some one order by on sequence , he should get an ordered marbles with matching glues.

    for example when 03 Marble gets glueid 8 and 9 , then i have to shift it to above 08 marble because 08 marble

    has glueid 9, 10 and marble 03 has just got gluetoid 9 which match with gluefromid 9 of marble 8

    similarly when any new marble gets pair of glueid only at that time i have to shift the sequenceno so that when ever any body puts order by he should get the correct order of marbles on the table(isotope).

    01,1,2,1

    02,2,3,2

    04,null,null,4

    05,null,null,5

    06,null,null,6

    07,null,null,7

    03,8,9,8

    08,9,10,9

    09,null,null,10

    10,null,null,11

    diagram on table will be like following where any marble can be attached any where to make a line.

    -0-0-0-0-0-0-0-

    it looks like ordered linked list

    yours sincerley

  • rajemessage 14195 (11/12/2016)


    following is the step:

    initially some sequence is given to marble table, at that time glueid in marble table are kept null.

    then one by one glue is attached to marble to make a line at that time glueid is entered in marble table.

    when ever glueid is entered in marble table, we are entering both glueid's gluefromid and gluetoid at a time or both will be null

    my requirment is to change the sequenceno according to the attached glue which can be attached to any marble.

    but one thing is finel that only two marble can use a glueid.

    so that when some one order by on sequence , he should get an ordered marbles with matching glues.

    for example when 03 Marble gets glueid 8 and 9 , then i have to shift it to above 08 marble because 08 marble

    has glueid 9, 10 and marble 03 has just got gluetoid 9 which match with gluefromid 9 of marble 8

    similarly when any new marble gets pair of glueid only at that time i have to shift the sequenceno so that when ever any body puts order by he should get the correct order of marbles on the table(isotope).

    01,1,2,1

    02,2,3,2

    04,null,null,4

    05,null,null,5

    06,null,null,6

    07,null,null,7

    03,8,9,8

    08,9,10,9

    09,null,null,10

    10,null,null,11

    diagram on table will be like following where any marble can be attached any where to make a line.

    -0-0-0-0-0-0-0-

    it looks like ordered linked list

    yours sincerley

    And still you refuse to ask a question. Are you expecting people here to help you with the entire solution, from start to finish? I'm sorry, but that is what paid consultants are for.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin (11/12/2016)


    And still you refuse to ask a question. Are you expecting people here to help you with the entire solution, from start to finish? I'm sorry, but that is what paid consultants are for.

    I sure am glad I'm not in school any more. 😉

  • Ed Wagner (11/12/2016)


    Phil Parkin (11/12/2016)


    And still you refuse to ask a question. Are you expecting people here to help you with the entire solution, from start to finish? I'm sorry, but that is what paid consultants are for.

    I sure am glad I'm not in school any more. 😉

    Yeah. I noticed that the OP refused to answer my second question in any way, shape, or form and still managed not to give much more useful information for my first question. Some useful information, yes, but I can't help looking at the answers and feeling it has to be homework given the totally lack of context (and OP-presented questions) in this thread.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 1)"Under what circumstances will changes be made to the glues on the marbles and the sequencing?"

    Glueid in marble table is only changed, When we want two marbles be connected to each other.

    ex if i want marble 03 to be connected to marble 8 , i will make following entry.

    Insert into MARBLE values('03',8,9,3)

    and shift the sequence no to 8

    Update MARBLE set seqnumber = 8 where marbleid =3

    and also will sift all seqnumber+1 including eighted marble onwards.

    so that sequenceno will be unique, and when any body will select from table using sequence no , he will get

    all marbles in an order.

    yours sincerely.

  • "Under what circumstances will changes be made to the glues on the marbles and the sequencing?"

    when ever i want to attach two marble we use same glueid.

    ex if i want marble 03 to be attached to marble 08 , the i will update

    update marble set gluefromid =8,gluetoid=9 where marbleid=3

    then i will find out where gluetoid is used , let us say it is 08 marble gluefromid.

    then i will update

    update marble set seqnumber =8 where marbleid =3

    update marble set sequnmber = sequnumber+1 where sequmber >=8 and marbleid !=3

    same thing, i will to with gluefromid of marbleid 3.

    yours sincerly.

  • For everyone's sake, this "problem" of re-determining the sequence numbers each time a new marble is added has been posted before, with the same relatively total lack of clarity. The poster seems to want to know how to change the sequence numbers after a new marble is brought into the chain, based on which glueid values are used for glueto and gluefrom. Frankly, using this kind of representation of this kind of problem, and having seen the word "isotope" involved, there's probably a lot more that we don't know about what this is being used for. Seeing as this is at least the 2nd posting of this problem, with no discernable difference in the statement of the problem, I'm thinking we're all better off not wasting any more time on it.

    That said, it seems to me that the representation here is hopeless, as there's no easy way to restrict the database data to ensure no glueid gets used more than once as a "from" and once as a "to". Maybe the poster should re-think the entire problem and come up with a different representation. As stated, there can be data conditions that will throw a query into an infinite loop, as I see recursion coming into this in such a way as to be a nightmare. I'm doubtful that there's a realistic solution or that if it does exist, would perform in a reasonable way, however, feel free to prove me wrong. I love to learn...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • sgmunson (11/22/2016)


    That said, it seems to me that the representation here is hopeless, as there's no easy way to restrict the database data to ensure no glueid gets used more than once as a "from" and once as a "to".

    Not entirely true. A unique constraint on GlueID in the connecting table would ensure the Glue only gets used once, but that would have to be a completely different table then what the OP has posted (s)he has.

    Other than that, I agree with your conclusion. The OP doesn't appear to understand what we're asking clarification on or is just refusing to give it. So it's difficult to help and is, at this point, a lost cause.

    If this is a RL work issue, I suggest the OP hire a local consultant who can sign an NDA and be on site to ask and answer the appropriate questions.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin (11/23/2016)


    sgmunson (11/22/2016)


    That said, it seems to me that the representation here is hopeless, as there's no easy way to restrict the database data to ensure no glueid gets used more than once as a "from" and once as a "to".

    Not entirely true. A unique constraint on GlueID in the connecting table would ensure the Glue only gets used once, but that would have to be a completely different table then what the OP has posted (s)he has.

    Other than that, I agree with your conclusion. The OP doesn't appear to understand what we're asking clarification on or is just refusing to give it. So it's difficult to help and is, at this point, a lost cause.

    If this is a RL work issue, I suggest the OP hire a local consultant who can sign an NDA and be on site to ask and answer the appropriate questions.

    We may be perceiving things differently, but used once is one thing, as opposed to used once one way AND used once another way, as well as it has to be both at the same time. That kind of representation is representative of procedural thinking as opposed to set-based logic, and it largely eliminates the benefits of using an RDBMS. It also doesn't take into account the more complex set of rules usually associated with "creating an isotope", which are usually bound by the inherent valence properties of the various atoms involved. Of course, what the poster actually means by the word "isotope" isn't addressed, and could certainly have a sizable impact on what's actually needed. Just for grins and giggles, I created an ATOMS table on my local machine and inserted some of the basic elements and their valence values from a copy of the periodic table I found online, that is fairly detailed. I then tried to think a little bit more procedurally, with the Valence values being a comma-separated string. Of course, I couldn't help but use DelimitedSplit8K to table-ize that information, and then a cartesian product to map a single positive valence element to a single negative valence element, and see how many unique combinations I could come up with where the sum of the valence values was equal to zero. It was an interesting exercise, but nothing I did there even remotely resembled what this poster seems to want. Without a good "why" ... I just can't get excited about trying to help the poster any further.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply