By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (APP, Ver, "DATE") AS
SELECT 'app1', '1.2', CAST(TIMESTAMP '2022-02-17 11:40:00' AS DATE) FROM DUAL UNION ALL
SELECT 'app1', '1.1', CAST(TIMESTAMP '2022-02-17 11:39:00' AS DATE) FROM DUAL UNION ALL
SELECT 'app2', '1.3', CAST(TIMESTAMP '2022-02-17 11:38:00' AS DATE) FROM DUAL UNION ALL
SELECT 'app3', '2.6', CAST(TIMESTAMP '2022-02-17 11:37:00' AS DATE) FROM DUAL UNION ALL
SELECT 'app3', '2.5', CAST(TIMESTAMP '2022-02-17 11:36:00' AS DATE) FROM DUAL UNION ALL
SELECT 'app2', '1.2', CAST(TIMESTAMP '2022-02-17 11:35:00' AS DATE) FROM DUAL UNION ALL
SELECT 'app4', '1.2', CAST(TIMESTAMP '2022-02-17 11:35:00' AS DATE) FROM DUAL UNION ALL
SELECT 'app4', '1.12', CAST(TIMESTAMP '2022-02-17 11:35:00' AS DATE) FROM DUAL
8 rows affected
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
SELECT *
FROM (
SELECT t.*,
ROW_NUMBER() OVER (
PARTITION BY app
ORDER BY TO_NUMBER(SUBSTR(ver, 1, INSTR(ver, '.') - 1)) DESC,
TO_NUMBER(SUBSTR(ver, INSTR(ver, '.') + 1)) DESC
) AS rn
FROM table_name t
)
WHERE rn = 1;
| APP | VER | DATE | RN |
|---|---|---|---|
| app1 | 1.2 | 2022-02-17 11:40:00 | 1 |
| app2 | 1.3 | 2022-02-17 11:38:00 | 1 |
| app3 | 2.6 | 2022-02-17 11:37:00 | 1 |
| app4 | 1.12 | 2022-02-17 11:35:00 | 1 |
SELECT *
FROM (
SELECT t.*,
ROW_NUMBER() OVER (
PARTITION BY app
ORDER BY TO_NUMBER(REGEXP_SUBSTR(ver, '^(\d+)\.(\d+)$', 1, 1, NULL, 1)) DESC,
TO_NUMBER(REGEXP_SUBSTR(ver, '^(\d+)\.(\d+)$', 1, 1, NULL, 2)) DESC
) AS rn
FROM table_name t
)
WHERE rn = 1;
| APP | VER | DATE | RN |
|---|---|---|---|
| app1 | 1.2 | 2022-02-17 11:40:00 | 1 |
| app2 | 1.3 | 2022-02-17 11:38:00 | 1 |
| app3 | 2.6 | 2022-02-17 11:37:00 | 1 |
| app4 | 1.12 | 2022-02-17 11:35:00 | 1 |