On Sep 16, 2014, at 8:15 PM, RSmith <rsm...@rsweb.co.za> wrote: > could you show how to achieve this in SQL via the ranking method you linked
Well, ranking is the same as numbering, no? So, for example: with NameSet as ( select 1 as id, 'paul' as name union all select 2 as id, 'helen' as name union all select 3 as id, 'melanie' as name ), CountrySet as ( select 1 as id, 'uk' as name union all select 20 as id, 'scotland' as name -- !!!! ), DataSet as ( select NameSet.id || '.' || CountrySet.id as key, NameSet.id as name_id, NameSet.name as name_name, CountrySet.id as country_id, CountrySet.name as country_name from NameSet cross join CountrySet ) select count( * ) as id, DataSet.name_id as name_id, DataSet.name_name as name_name, DataSet.country_id as country_id, DataSet.country_name as country_name from DataSet join DataSet self on self.key >= DataSet.key group by DataSet.name_id, DataSet.name_name, DataSet.country_id, DataSet.country_name order by 1; > id|name_id|name_name|country_id|country_name > 1|3|melanie|2|scotland > 2|3|melanie|1|uk > 3|2|helen|2|scotland > 4|2|helen|1|uk > 5|1|paul|2|scotland > 6|1|paul|1|uk Or something :D _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users