By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable(
OrderNumber INTEGER NOT NULL
,Line INTEGER NOT NULL
,Item VARCHAR(14) NOT NULL
,Warehouse VARCHAR(3) NOT NULL
,Carrier VARCHAR(5) NOT NULL
,Quantity INTEGER NOT NULL
);
INSERT INTO mytable(OrderNumber,Line,Item,Warehouse,Carrier,Quantity) VALUES (255,1,'STUFFED-ANIMAL','WH1','UPS',3);
INSERT INTO mytable(OrderNumber,Line,Item,Warehouse,Carrier,Quantity) VALUES (256,1,'BLOCKS','WH2','FEDEX',1);
INSERT INTO mytable(OrderNumber,Line,Item,Warehouse,Carrier,Quantity) VALUES (257,1,'DOLL','WH1','UPS',1);
INSERT INTO mytable(OrderNumber,Line,Item,Warehouse,Carrier,Quantity) VALUES (257,2,'DRESS','WH1','UPS',3);
INSERT INTO mytable(OrderNumber,Line,Item,Warehouse,Carrier,Quantity) VALUES (257,3,'SHOES','WH2','UPS',1);
INSERT INTO mytable(OrderNumber,Line,Item,Warehouse,Carrier,Quantity) VALUES (258,1,'CHAIR','WH3','FEDEX',1);
INSERT INTO mytable(OrderNumber,Line,Item,Warehouse,Carrier,Quantity) VALUES (258,1,'CHAIR','WH3','UPS',2);
7 rows affected
SELECT (case when seqnum_asc + seqnum_desc - 1 > 1 -- more than 1 distinct value
then concat(ordernumber, '-', seqnum_asc)
else concat(ordernumber, '') -- just to convert the value to a string
end) as packagenumber,
t.*
FROM (select t.*,
dense_rank() over (partition by ordernumber order by warehouse, carrier) as seqnum_asc,
dense_rank() over (partition by ordernumber order by warehouse desc, carrier desc) as seqnum_desc
from mytable t
) t
| packagenumber | OrderNumber | Line | Item | Warehouse | Carrier | Quantity | seqnum_asc | seqnum_desc |
|---|---|---|---|---|---|---|---|---|
| 255 | 255 | 1 | STUFFED-ANIMAL | WH1 | UPS | 3 | 1 | 1 |
| 256 | 256 | 1 | BLOCKS | WH2 | FEDEX | 1 | 1 | 1 |
| 257-2 | 257 | 3 | SHOES | WH2 | UPS | 1 | 2 | 1 |
| 257-1 | 257 | 1 | DOLL | WH1 | UPS | 1 | 1 | 2 |
| 257-1 | 257 | 2 | DRESS | WH1 | UPS | 3 | 1 | 2 |
| 258-2 | 258 | 1 | CHAIR | WH3 | UPS | 2 | 2 | 1 |
| 258-1 | 258 | 1 | CHAIR | WH3 | FEDEX | 1 | 1 | 2 |