Simon, On Thu, Oct 31, 2013 at 8:51 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 1 Nov 2013, at 2:15am, Igor Korot <ikoro...@gmail.com> wrote: > >> When the record is inserted with the field1 as NULL, I want to have >> the field1 to have value max( field1 ) + 1. >> When the record inserted have some value in field1, i.e. field1 == 5, >> I want all records that have field1 > 5 to have field1 to be >> incremented. So if new record have field1 as 5, so the old record with >> field1 == 5 will have field1 = 6, field1 will become 7 and so forth. > > You are trying to fake an explicit order for your rows by doing some very > complicated things when you insert a new row. This is not a plausible way to > handle a database and consequently it's very difficult in SQL. It can also > require lots of processing and disk activity: what happens when you insert a > new row with field1 = 1 in a table which has 10000 entries ?
I'm not trying to change an order of my rows. Look at it this way: I have a league with some teams. When the league is created the players needs to be put in from the players pool. In order to assign the rank inside the league I am inserting players with the rank as NULL. In this case will be assigned as 1,2,3,4,etc based on the player pool rank. Now it is possible to insert a new player in the league. If I insert a new player with the value of 0 then the same rule apply - the rank of the new player will be max( rank ) + 1. Otherwise the new player will be inserted by the value. So if I insert the new player with the value of $5.00 and there are players with the values $0 - $5 they needs to be moved in rank. Is it more clear now? Thank you. > > Pretend triggers didn't exist and try to figure out how you would usefully > have SQL order your rows for you. Go back to first principles: what are you > really trying to do ? > > If you're just allowing your users to rearrange roworder, use REAL values > instead of INTEGERs for the roworder field, and when you insert a new row > between two existing ones, just give it a roworder of the mean of the two > rows surrounding it. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
