How to select all PK's (column 1) where the MAX(ISNULL(value, 0)) in column 3 grouped by a value in column 2?

Question

I couldn't find an answer on my question since all questions similar to this one aren't using a nullable int in the max value and getting 1 column out of it.

My table is as follows:

| ContractId | ContractNumber | ContractVersion |    
+------------+----------------+-----------------+    
|          1 |             11 |            NULL |  
|          2 |             11 |               1 |  
|          3 |             11 |               2 |  
|          4 |             11 |               3 | --get this one 
|          5 |             24 |            NULL |  
|          6 |             24 |               1 | --get this one 
|          7 |             75 |            NULL | --get this one

The first version is NULL and all following versions get a number starting with 1.

So now I only want to get the rows of the latest contracts (as shown in the comments behind the rows).

So for each ContractNumber I want to select the ContractId from the latest ContractVersion.

The MAX() function wont work since it's a nullable int.

So I was thinking to use the ISNULL(ContractVersion, 0) in combination with the MAX() function, but I wouldn't know how.

I tried the following code:

SELECT 
    ContractNumber,
    MAX(ISNULL(ContractVersion, 0))
FROM
    Contracts
GROUP BY 
    ContractNumber

...which returned all of the latest version numbers combined with the ContractNumber, but I need the ContractId. When I add ContractId in the SELECT and the GROUP BY, I'm getting all the versions again.

The result should be:

| ContractId |
+------------+
|          4 |
|          6 |
|          7 |

@juergend The difference is that in your link there's a group by on the PK, not on an other column. That's where I'm struggling. — remkovdm, Dec 14 '18 at 08:45

score 2 · Accepted Answer · answered Dec 14 '18 at 08:44

It's just a simple application of ROW_NUMBER() when you're wanting to select rows based on Min/Max:

declare @t table (ContractId int, ContractNumber int, ContractVersion int)
insert into @t(ContractId,ContractNumber,ContractVersion) values
(1,11,NULL ),
(2,11,   1 ),
(3,11,   2 ),
(4,11,   3 ),
(5,24,NULL ),
(6,24,   1 ),
(7,75,NULL )

;With Numbered as (
    select *,ROW_NUMBER() OVER (
             PARTITION BY ContractNumber
             order by ContractVersion desc) rn
    from @t
)
select
    *
from
    Numbered
where rn = 1

score 0 · Answer 2 · answered Dec 14 '18 at 08:44

0

this will work:

select ContractId,max(rank),ContractNumber from(select *,rank() over(partition by 
ContractVersion order by nvl(ContractVersion,0)) desc ) rank from tablename)  group by 
ContractId,max(rank),ContractNumber;

answered Dec 14 '18 at 08:44

Nikhil S

3,786
4
18
32

How to select all PK's (column 1) where the MAX(ISNULL(value, 0)) in column 3 grouped by a value in column 2?

2 Answers2