On Tue, 5 Feb 2013 01:22:37 +0000
YAN HONG YE <[email protected]> wrote:
> I hava a table like this:
> id,name,score,rank
> 1,anna,80,0
> 2,qera,65,0
> 6,kero,90,0
> 10,rosa,95,0
>
> what I would like to do is to update the rank position. I have this,
The rank can be derived, obviating the need to update it:
create view vscores as
select 1 + count(b.score) as rank
, a.name
, a.score
, a.id
from scores as a
left join scores as b
on a.score < b.score
group by a.name
, a.score
, a.id
;
sqlite3 scores.db 'select * from vscores order by score desc;'
rank name score id
---------- ---------- ---------- ----------
1 rosa 95.0 10
2 kero 90.0 6
3 anna 80.0 1
4 qera 65.0 2
--jkl
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
