By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table myTable(id0 int, id1 int, ORDER_NUMBER varchar(20), ORDER_NUMBER_2 varchar(20), Type varchar(5), RN int);
insert into myTable values
(47, 48 , '2000036' , '0' ,'A', 1),
(47 , 49 , '300026660000' , '0' ,'B', 2),
(47 , 49 , '300026660001' , '300026660000' ,'C', 3),
(250 , 251 , '2000341', '0' ,'X', 1),
(250 , 252 , '300000193000', '0' ,'Y', 2),
(250 , 252 , '300000193001', '300000193000' ,'Z', 3)
6 rows affected
SELECT T.Type_main,T.Type_minor,T.ORDER_NUMBER_main,T.ORDER_NUMBER_minor
FROM
(SELECT
t1.Type AS Type_main,
t2.Type AS Type_minor,
t1.ORDER_NUMBER AS ORDER_NUMBER_main,
t2.ORDER_NUMBER AS ORDER_NUMBER_minor,
ROW_NUMBER() OVER(PARTITION BY t1.Type ORDER BY t1.id0) seq
FROM myTable t1
JOIN
myTable t2 ON t1.id0 = t2.id0 and t1.RN < t2.RN) T
WHERE T.seq = 1
| Type_main | Type_minor | ORDER_NUMBER_main | ORDER_NUMBER_minor |
|---|---|---|---|
| A | B | 2000036 | 300026660000 |
| B | C | 300026660000 | 300026660001 |
| X | Y | 2000341 | 300000193000 |
| Y | Z | 300000193000 | 300000193001 |