Oracle 21c

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

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