I have this table `ScoreDetails`, 2 columns (there are more, but only 2 needed or this query). One is `ScoreDate`, `Score`.

The structure is like

```
2012:03:27: 5:06:37:134           27
2012:03:27: 5:06:37:276           37
2012:03:28: 4:12:97:019           19
2012:03:29: 7:06:37:134           7
2012:03:29: 8:06:37:134           0
2012:04:03: 12:06:37:739          16
2012:04:04: 23:21:15:834          33
2012:04:04: 15:08:24:697          12
2012:04:06: 5:06:37:134           0
2012:04:09: 5:06:37:134           2
2012:04:13: 5:06:37:134           92

```

What I want is to write a select query, **without** using temp table or cursor. Such that, I have a column that starts from 1 and keeps on increasing as 2,3 and so on, upto when the score is non-zero. But as soon as a zero is encountered in score column, it resets to 1 and then start again. Like this...

```
2012:03:27: 5:06:37:134           27       1
2012:03:27: 5:06:37:276           37       2
2012:03:28: 4:12:97:019           19       3
2012:03:29: 7:06:37:134           7        4
2012:03:29: 8:06:37:134           0        0
2012:04:03: 12:06:37:739          16       1
2012:04:04: 23:21:15:834          33       2
2012:04:04: 15:08:24:697          12       3
2012:04:06: 5:06:37:134           0        0
2012:04:09: 5:06:37:134           2        1
2012:04:13: 5:06:37:134           92       2

```

I am using SQL Server 2008 R2.