Re: [sqlite] Re; Subrank query
Rick Guizawa wrote: i have a table like: score| rank | game 98 | 1 | 1615 98 | 1 | 1615 92 | 2 | 1615 87 | 3 | 1615 87 | 3 | 1615 87 | 3 | 1615 112 | 1 | 1616 94 | 2 | 1616 94 | 2 | 1616 I want to have a query to produce : score | rank | subrank | game 98| 1 | 1 | 1615 98| 1 | 2 | 1615 92| 2 | 1 | 1615 87| 3 | 1 | 1615 87| 3 | 2 | 1615 87| 3 | 3 | 1615 112 | 1 | 1 | 1616 94| 2 | 1 | 1616 94| 2 | 2 | 1616 So, the subrank is the number of records up until the current record in the same rank: SELECT score, rank, (SELECT count(*) FROM scores s2 WHERE s2.rank = s1.rank AND s2.game = s1.game AND s2.rowid = s1.rowid) AS subrank, game FROM scores s1 Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Re; Subrank query
Dear Friends, please help with sqlite query, i have a table like: score| rank | game 98| 1 |1615 98| 1 |1615 92| 2 |1615 87| 3 |1615 87| 3 |1615 87| 3 |1615 112 | 1 |1616 94| 2 |1616 94| 2 |1616 I want to have a query to produce : score | rank | subrank | game 98 | 1 | 1 | 1615 98 |1| 2 | 1615 92 |2| 1 | 1615 87 | 3 | 1 | 1615 87 | 3 | 2 | 1615 87 | 3 | 3 | 1615 112 | 1 |1 | 1616 94| 2 | 1 | 1616 94| 2 |2 | 1616 Thank's in advance for your help. Ricky ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Re; Subrank query
prob. u want something like SELECT t1.score, t1.rank, t2.subrank, t1.game FROM table1 t1 LEFT JOIN table2 t2 ON t1.game = t2.game (if game is the common column on both tables else use the common element) 2012/10/29 Rick Guizawa guizaw...@gmail.com Dear Friends, please help with sqlite query, i have a table like: score| rank | game 98| 1 |1615 98| 1 |1615 92| 2 |1615 87| 3 |1615 87| 3 |1615 87| 3 |1615 112 | 1 |1616 94| 2 |1616 94| 2 |1616 I want to have a query to produce : score | rank | subrank | game 98 | 1 | 1 | 1615 98 |1| 2 | 1615 92 |2| 1 | 1615 87 | 3 | 1 | 1615 87 | 3 | 2 | 1615 87 | 3 | 3 | 1615 112 | 1 |1 | 1616 94| 2 | 1 | 1616 94| 2 |2 | 1616 Thank's in advance for your help. Ricky ___ 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
Re: [sqlite] Re; Subrank query
See this topic http://www.w3schools.com/sql/sql_join.asp for more information =S and sry about my english =D 2012/10/29 Caio Honma caio.ho...@gmail.com prob. u want something like SELECT t1.score, t1.rank, t2.subrank, t1.game FROM table1 t1 LEFT JOIN table2 t2 ON t1.game = t2.game (if game is the common column on both tables else use the common element) 2012/10/29 Rick Guizawa guizaw...@gmail.com Dear Friends, please help with sqlite query, i have a table like: score| rank | game 98| 1 |1615 98| 1 |1615 92| 2 |1615 87| 3 |1615 87| 3 |1615 87| 3 |1615 112 | 1 |1616 94| 2 |1616 94| 2 |1616 I want to have a query to produce : score | rank | subrank | game 98 | 1 | 1 | 1615 98 |1| 2 | 1615 92 |2| 1 | 1615 87 | 3 | 1 | 1615 87 | 3 | 2 | 1615 87 | 3 | 3 | 1615 112 | 1 |1 | 1616 94| 2 | 1 | 1616 94| 2 |2 | 1616 Thank's in advance for your help. Ricky ___ 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
