I just tried to do: SELECT max(current_rank) FROM playersinleague WHERE id = 1;
and I got an empty string and not 0. Is this a bug? Should max(field) return 0 if there is no records that satisfy criteria? Thank you. On Sat, Nov 9, 2013 at 9:54 PM, Igor Korot <ikoro...@gmail.com> wrote: > Hi, ALL, > > CREATE TABLE playersinleague( > id integer > playerid integer > ishitter char > age integer > value double > currvalue double > draft boolean > isnew char(1) > current_rank integer > original_rank integer > deleted integer > foreign key(id) references leagues(id) > foreign key(playerid) references players(playerid) > ); > > CREATE TRIGGER playersinleague_insert AFTER INSERT on playersinleague > BEGIN > UPDATE playersinleague SET current_rank = 1+ (select max( > current_rank ) from playersinleague WHERE id = new.id), original_rank > = current_rank WHERE id = new.id AND current_rank IS NULL; > END; > > or do it this way: > > DROP TRIGGER playersinleague_insert; > CREATE TRIGGER playersinleague_insert AFTER INSERT ON playersinleague > BEGIN > UPDATE playersinleague SET current_rank = 1 + (select max( > current_rank ) FROM playersinleague WHERE id = new.id), original_rank > = current_rank WHERE rowid = new.rowid; > END; > > After that: > > INSERT INTO playersinleague(id,playerid,ishitter,age,value,currvalue,dra > ft,isnew,deleted) SELECT 1, players.playerid, players.ishitter, players.age, > pla > yers.value, players.value, 0, "0", 0 FROM players WHERE (teamid >= 1 AND > teamid > <= 15) OR (teamid >= 16 AND teamid <= 30) ORDER BY rank; > > SELECT * FROM playersinleague WHERE id=1 AND playerid=1; > 1|1|1|27|42.0|42.0|0|0|||0 > > I didn't specify current_rank on the INSERT query which means the > field shuold be NULL. > > I think I am doing something wrong but I don't see what. > > Could someone please help? > Also will the UPDATE in trigger do the right thing and correctly > update the original_rank? > > Thank you. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
