SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table yourtable (orderId int, code varchar(100), val varchar(100));
insert into yourtable values (1,'Code1','abc');
insert into yourtable values (2,'Code2','def');
insert into yourtable values (3,'Code1','aaa');
insert into yourtable values (3,'Code2','bbb');
insert into yourtable values (4,'Code10','aaa');
insert into yourtable values (4,'Code2','bbb');
6 rows affected

SELECT * FROM yourtable;


orderId
code
val


1
Code1
abc

2
Code2
def

3
Code1
aaa

3
Code2
bbb

4
Code10
aaa

4
Code2
bbb


SELECT 
OrderId, 
MAX(Code) AS Code
FROM yourtable
GROUP BY OrderId
ORDER BY OrderId;


OrderId
Code


1
Code1

2
Code2

3
Code2

4
Code2


SELECT 
y.OrderId, 
y.Code, 
y.Val
FROM yourtable y
INNER JOIN
(SELECT 
OrderId, 
MAX(Code) AS Code
FROM yourtable
GROUP BY OrderId) AS sub 
ON y.OrderId = sub.OrderId
AND y.Code = sub.Code
ORDER BY y.OrderId;


OrderId
Code
Val


1
Code1
abc

2
Code2
def

3
Code2
bbb

4
Code2
bbb


SELECT
OrderId,
MAX(CAST(SUBSTRING(Code,5,LEN(code)-4) AS INT)) AS code
FROM yourtable
GROUP BY OrderId;


OrderId
code


1
1

2
2

3
2

4
10


WITH maxCodes AS
(SELECT
OrderId,
MAX(CAST(SUBSTRING(Code,5,LEN(code)-4) AS INT)) AS Code
FROM yourtable
GROUP BY OrderId)
SELECT
y.OrderId, 
y.Code, 
y.Val
FROM yourtable y
INNER JOIN maxCodes m
ON y.OrderId = m.OrderId
AND CAST(SUBSTRING(y.Code,5,LEN(y.Code)-4) AS INT) = m.Code


OrderId
Code
Val


1
Code1
abc

2
Code2
def

3
Code2
bbb

4
Code10
aaa


SELECT orderId, code, val
FROM (
  SELECT 
    orderId, code, val,
    ROW_NUMBER() OVER 
      (PARTITION BY orderId 
      ORDER BY CAST(SUBSTRING(Code,5,LEN(code)-4) AS INT) desc) row_num
    FROM yourtable
) Orders
WHERE row_num = 1


orderId
code
val


1
Code1
abc

2
Code2
def

3
Code2
bbb

4
Code10
aaa