I am trying to create groups for each Name and save the max and min ID for each group. The new group starts if the Length is higher then the previous Length for a particular Name, ordered by ID.
The source data table is as follow:
CREATE TABLE [dbo].[TEST](
[Name] [nvarchar](50) NOT NULL,
[Length] [int] NOT NULL,
[ID] [int] NOT NULL
) ON [PRIMARY]
INSERT INTO [dbo].[TEST]
The result table should look like this:
I approached this like a problem of finding the islands. The first island for Name = A is defined by Length 30 to 20. The second island is again for Name = A but Length 25 to 20.
I used Row_number() function but I don’t know how to create the proper partitions: in the example above, we have two partitions for the Name = A, for example.
Thanks for help.
- This topic was modified 1 month, 3 weeks ago by itlk. Reason: Missing script to create a sample table
- This topic was modified 1 month, 3 weeks ago by itlk.