SQL Keep (dense_rank last order by) to H2 database

Question

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?

Answer 1

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.

Answer 2

The closest equivalent is something like this:

SELECT 
c.car_id
SUBSTRING(
    MAX(
        DECODE(m.mod_number 'SP1', '10', 'SP2', '20', 'SP3', '30', '40')) 
        || 
        TO_CHAR(m.mod_number)
    ),
3,100) NewField
FROM CAR c, MANUFACTURE m
WHERE c.car_type = m.car_type
AND m.make LIKE 'FOR%'
GROUP BY c.car_id;

KEEP is a way of picking a single record out of many on a different basis (order) to the actual recordset you are using. Luckily this particular query does't necessarily need it.

Try this in Oracle first before you try to convert to H2