By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Create the table
CREATE TABLE DeviceImpact (
Device_ID VARCHAR(10),
Impact_Desc VARCHAR(50),
SR_Number INT NULL,
Category VARCHAR(50) NULL,
Duration_Hrs INT
);
-- Insert the data
INSERT INTO DeviceImpact (Device_ID, Impact_Desc, SR_Number, Category, Duration_Hrs)
VALUES
('A1', 'Unavailable', 1234, 'Type1', 10),
('A1', 'Unavailable', NULL, 'Type2', 8),
('A1', 'Unavailable', NULL, NULL, 20),
('A1', 'Wounded', NULL, 'Type2', 5),
('A1', 'Wounded', NULL, NULL, 5),
('B1', 'Unavailable', NULL, 'Type1', 7),
('B1', 'Unavailable', NULL, 'Type1', 15),
('B1', 'Wounded', 4567, NULL, 4),
('C1', 'Wounded', NULL, NULL, 2);
9 rows affected
WITH cte AS
(
SELECT
*,
ROW_NUMBER() OVER
(
PARTITION BY
Device_ID,
Impact_Desc
ORDER BY
CASE
WHEN SR_Number IS NOT NULL
THEN 1 ELSE 2
END,
Duration_Hrs DESC,
CASE
WHEN Category IS NOT NULL
THEN 1 ELSE 2
END
) AS Row_Num
FROM DeviceImpact
)
SELECT *
FROM cte
WHERE Row_Num = 1
| Device_ID | Impact_Desc | SR_Number | Category | Duration_Hrs | Row_Num |
|---|---|---|---|---|---|
| A1 | Unavailable | 1234 | Type1 | 10 | 1 |
| A1 | Wounded | null | Type2 | 5 | 1 |
| B1 | Unavailable | null | Type1 | 15 | 1 |
| B1 | Wounded | 4567 | null | 4 | 1 |
| C1 | Wounded | null | null | 2 | 1 |