CODEWITHSUNDEEP
sqloracle-databaseh2
logger
logger

Reputation: 2053

SQL Keep (dense_rank last order by) to H2 database

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?

Upvotes: 0

Views: 1030

Answers (2)

Nick.Mc
Nick.Mc

Reputation: 19184

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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.

Upvotes: 2

Related Questions