On 2014/09/16 20:00, James K. Lowden wrote:
Or not use a temporary table.
http://www.schemamania.org/sql/#rank.rows
Now easier with CTE. In theory it could be *faster* than a temporary
table, because the insertion I/O is avoided. But only testing will
tell.
--jkl
Hi James,
I'm well familiar with the ranking SQL (thanks to your site btw), but I fail to see how that can be applied to solve the OP's
problem which I will list again hereunder. I know the OP in the meantime realized he had other problems which renders any of these
solutions unhelpful, but allow me the learning experience please, could you show how to achieve this in SQL via the ranking method
you linked (or any other method you might come up with - I can do it in CTE, but am hoping to learn how to do without):
say for instance I have two tables
create tab1 (id int, name text)
1, 'paul'
2, 'helen'
3, 'melanie'
create tab2 (id int, country text)
1, 'uk'
2, 'scotland'
what I want is
1 | 1 | paul | 1 | uk
2 | 1 | paul | 2 | scotland
3 | 2 | helen | 1 | uk
4 | 2 | helen | 2 | scotland
5 | 3 | melanie | 1 | uk
6 | 3 | melanie | 2 | scotland
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users