I have following select statement

```
SELECT c.car_id
MAX(m.mod_number) KEEP (DENSE_RANK LAST ORDER BY
DECODE(m.mod_number 'SP1', 10, 'SP2', 20, 'SP3', 30, 40))
FROM CAR c, MANUFACTURE m
WHERE c.car_type = m.car_type
AND m.make LIKE 'FOR%'
GROUP BY c.car_id;
```

I wanted to change it into H2 grammar for unit testing but I only able to do with decode. I have no idea how to modify with the keep (dense_rank...) How am I able to do it while retaining Oracle's functionality of this select statement?

# Best How To :

I think this might work:

```
SELECT c.car_id,
(CASE MAX(CASE WHEN m.mod_number = 'SP1' THEN 10
WHEN m.mod_number = 'SP2' THEN 20
WHEN m.mod_number = 'SP3' THEN 30
ELSE 40
END)
WHEN 10 THEN 'SP1'
WHEN 20 THEN 'SP2'
WHEN 30 THEN 'SP3'
ELSE MAX(CASE WHEN m.mod_number NOT IN ('SP1', 'SP2', 'SP3') THEN m.mod_number
END)
END)
FROM CAR c JOIN
MANUFACTURE m
ON c.car_type = m.car_type
WHERE m.make LIKE 'FOR%'
GROUP BY c.car_id;
```

It calculate the maximum priority and then maps the value back to the original value.