1

I am using SQL Server 2019 and I need to process the latest row from a group of rows. However, I need to mark the other rows in that group so I know they where evaluated when the latest row was processed. The actual problem is complicated, with many columns and is partitioned on json column.

I have created a simplier example to illustrate the problem.

Given this table,

CREATE TABLE [dbo].[test](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [val] [nvarchar](50) NOT NULL,
    [ord] [int] NOT NULL
)

With this data,

insert into test(val, ord)
values ('A', 1),
('A', 2),
('B', 1),
('B', 3)



id  val ord
1   A   1
2   A   2
3   B   1
4   B   3

I need to know the highest row in each group, and know what other rows make up that group. I can use the query below, and rownumber = 1 will give the row with the higest ord in each partition (id 2,4).

select *, ROW_NUMBER() over (partition by val ORDER BY ord DESC) as rownumber
from test

I also need to know that id=1 was in the same partition with id=2 (the A partition), and that id=3 was in the same partition with id=4 (the B partition).

How can I find the other rows in the same partition?

Update:

I want to process the row with the highest ord value, and then delete all the rows in that partition that have a lower ord value. I can't use the same partition crteria in another sql statement because rows are constantly being added.

I am looking for results like this:

id  val ord rownumber   ThePartition
2   A   2      1         1
1   A   1      2         1
4   B   3      1         2
3   B   1      2         2

The query above gerenates the rownumber, but I can't find a way to create the 'ThePartition' column. That column as one value for the first partition, another value for the second partition, and so own. I just need a value to identify the partition, it doesn't have to be a sequential integer.

Don Chambers
  • 3,798
  • 9
  • 33
  • 74
  • I'm probably missing something, but you are the one defining the partitions with the `partition by` clause that you're providing to the `row_number()` function. So, in this case, all rows with the same value for that column are in the same partition and the one with rownumber = 1. – Ben Thul Nov 16 '22 at 17:08
  • I can comntrol the partition, but I have that as I need it. I have updated the question to explain it better. Row_Number will number the rows in the order of the order by clause. I need anohter column that will givee the same value for each row in the partition. – Don Chambers Nov 16 '22 at 17:45

1 Answers1

1

I think the function you are looking for is DENSE_RANK.

select  *, 
        ROW_NUMBER() over (partition by val ORDER BY ord DESC) as rownumber,
        DENSE_RANK() Over (Order BY Val) As ThePartition
from    Test;
George Mastros
  • 24,112
  • 4
  • 51
  • 59