Getting the first instance of a zero value in a table of records-CodePudding

I have many horses and their values which reduce over time. I would like to be able to fetch only the rows where the value of each horse becomes 0 for the first time. There are multiple rows for each horse where the values are 0 but I want the earliest of them. My table looks like this and I need to return the rowID and the others if possible (8 and 20 in this example). I tried using the rank over partition but could not get that to work for me and I would paste the example but frankly it was rubbish. Thank you in advance.

rowID	horseID	valueYear	valueMonth	value	I need these rows
1	1	1990	7	1000
2	1	1991	1	900
3	1	1992	2	800
4	1	1993	4	700
5	1	1993	7	690
6	1	1995	3	500
7	1	1995	7	470
8	1	1997	8	0	<----
9	1	1998	2	0
10	1	1999	3	0
11	1	2000	9	0
12	2	1990	3	900
13	2	1991	1	750
14	2	1992	7	700
15	2	1993	3	600
16	2	1993	4	590
17	2	1995	3	300
18	2	1995	9	170
19	2	1997	5	50
20	2	1998	6	0	<----
21	2	1999	4	0
22	2	2000	2	0

CodePudding user response：

One option is WITH TIES in concert with row_number()

Select top 1 with ties *
 From  YourTable
 Where value = 0
 Order By row_number() over (partition by horseID order by rowID ) -- could be valueYear as well