SQL row number partitioned by a repeated value

Question

I am looking for a way to flag and reset a row number on rows that repeated before in the table, but only to have one row as the basis of comparison. In the example below, the first row (vertex ID 1) should be the base row for comparison until it finds a match based on the ObjID, Lat, Lon columns (vertex ID 4). Then the next row (vertex ID 5) is the basis for comparison until the next match (vertex ID 8).

ObjID	VertexID	Lat	Lon	Reader Notes
1	1	30.1	-100.1	Polygon 1 st.
1	2	30.2	-100.2
1	3	30.3	-100.2
1	4	30.1	-100.1	Polygon 1 end
1	5	30.4	-100.4	Polygon 2 st.
1	6	30.5	-100.4
1	7	30.5	-100.5
1	8	30.4	-100.4	Polygon 2 end

Ideally, I would have another column that counts 1...4 for the first polygon and 1...4 for the second as well. I already have the SQL to count off polygon numbers (e.g. four 1s and four 2s).

As you might infer from the example, I have a list of all the points in a multipolygon but without any indicator on when the vertices start and end for each of the multipolygon shapes start other than the fact that the first and last vertices must be the same.

Please add it to this [DBFIDDLE](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e1fd126245ba07dbd29dcd54a53b62d2), and share the new link..... — Luuk, Jun 12 '22 at 15:24
Are you condering the logic should consider the rows sorted by `VortexID`? Is this column unique? — The Impaler, Jun 12 '22 at 15:49

score 2 · Accepted Answer · answered Jun 12 '22 at 16:03

2

You can do it with a recursion . For the sample data I assume VertexID is unique and it is a continues series of integers. Otherwise first row_number() data accordingly.

with poly as (
   select ObjID, VertexID, Lat, Lon, polyN=1, flag=0, sObjID=ObjID, sLat=Lat, sLon=Lon
   from tbl
   where VertexID =1
   
   union all 
   
   select  t.ObjID, t.VertexID, t.Lat, t.Lon, polyN + flag, 
      case when t.ObjID = p.sObjID and t.Lat = p.sLat and t.Lon = p.sLon then 1 else 0 end,
      case flag when 1 then t.ObjID else sObjID end,
      case flag when 1 then t.Lat else sLat end,
      case flag when 1 then t.Lon else sLon end
 
   from poly p
   join tbl t on t.VertexID = p.VertexID + 1
)
select ObjID, VertexID, Lat, Lon, polyN
from poly

answered Jun 12 '22 at 16:03

Serg

22,285
5
21
48

So I tried this method, and I get the error "maximum recursion 100 has been exhausted before statement completion" - not sure if its a data error on my side or in the query. I will look into this later. The dataset I was using had about 5000 points for one multipolygon with about 5 or 6 separate polygons. – siromega Jun 13 '22 at 14:46
see: [The maximum recursion 100 has been exhausted before statement completion](https://stackoverflow.com/questions/9650045/the-maximum-recursion-100-has-been-exhausted-before-statement-completion). Which says: add `option (maxrecursion 0)` to your statement (or another number than 0, but than you have to start reading.... ) – Luuk Jun 13 '22 at 17:24
This works when removing max recursion limits. This solves my question, however to put into production I may need to do it in such a way that works for 5,000 and 10,000 point multipolygons. Marked as solution. Thank you! – siromega Jun 14 '22 at 12:52

SQL row number partitioned by a repeated value

1 Answers1