On Sep 16, 2014, at 8:15 PM, RSmith <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
