sqlite query to add index for results -

i have table include 2 parameters: group_id & name

create table inf_table (group_id integer not null, name text not null); insert  inf_table(group_id, name) values (1,'aa'); insert  inf_table(group_id, name) values (1,'bb'); insert  inf_table(group_id, name) values (1,'ab'); insert  inf_table(group_id, name) values (2,'aa'); insert  inf_table(group_id, name) values (2,'ba'); insert  inf_table(group_id, name) values (2,'ab'); insert  inf_table(group_id, name) values (2,'bb'); 

i run query order result group_id , name. " select * inf_table order group_id,name;" , got:

1|aa 1|ab 1|bb 2|aa 2|aa 2|ba 2|bb 

but, add index each row display index of name inside each group:

1|1|aa 2|1|ab 3|1|bb 1|2|aa 2|2|aa 3|2|ba 4|2|bb 

how i?

thanks

you have count rows in same group , have name before current name:

select (select count(*)         inf_table inf2         inf2.group_id = inf_table.group_id           , inf2.name    <= inf_table.name    ) "index",        group_id,        name inf_table order group_id,          name 

(it might more efficient count rows when read them in program.)