Rick Guizawa wrote:
> i have a table like:
>
> score| rank | game
> 98 | 1 | 1615
> 98 | 1 | 1615
> 92 | 2 | 1615
> 87 | 3 | 1615
> 87 | 3 | 1615
> 87 | 3 | 1615
> 112 | 1 | 1616
> 94 | 2 | 1616
> 94 | 2 | 1616
>
> I want to have a query to produce :
>
> score | rank | subrank | game
> 98 | 1 | 1 | 1615
> 98 | 1 | 2 | 1615
> 92 | 2 | 1 | 1615
> 87 | 3 | 1 | 1615
> 87 | 3 | 2 | 1615
> 87 | 3 | 3 | 1615
> 112 | 1 | 1 | 1616
> 94 | 2 | 1 | 1616
> 94 | 2 | 2 | 1616
So, the subrank is the number of records up until the current record
in the same rank:
SELECT score,
rank,
(SELECT count(*)
FROM scores s2
WHERE s2.rank = s1.rank
AND s2.game = s1.game
AND s2.rowid <= s1.rowid) AS subrank,
game
FROM scores s1
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
