How to query the latest version for each app?-CodePudding

I have the following table with apps,versions and release dates:

note that it is impossible to have a lower version being release after a newer one. (we would never downgrade versions)

APP	Ver	Date
app1	1.2	2/17/2022 11:40am
app1	1.1	2/17/2022 11:39am
app2	1.3	2/17/2022 11:38am
app3	2.6	2/17/2022 11:37am
app3	2.5	2/17/2022 11:36am
app2	1.2	2/17/2022 11:35am

And I want the latest version for each app output:

APP	Ver	Date
app1	1.2	2/17/2022 11:40am
app2	1.3	2/17/2022 11:38am
app3	2.6	2/17/2022 11:37am

Im having trouble with the group by, or if there is an easier way Ill be thankful.

CodePudding user response：

row_number analytic function will "sort" them by version in descending order; then select those that rank as "highest":

with temp as
  (select app, ver, datum,
     row_number() over (partition by app order by datum desc) rn
   from your_table
  )
select app, ver, datum
from temp
where rn = 1;

CodePudding user response：

You can split the version into the major, minor (and, if it exists, patch) version numbers and order by each of those:

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;

Or, using (slower) regular expressions:

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;

Which, for the sample data:

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

Both output:

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

db<>fiddle here

CodePudding user response：

One option would be using MAX(..) KEEP (DENSE_RANK ..) analytic function without need of any subquery in order to get the latest per each app group such as

SELECT app AS "App",
       MAX(ver) KEEP (DENSE_RANK LAST ORDER BY "date") AS "Ver",
       MAX("date") KEEP (DENSE_RANK LAST ORDER BY "date") AS "Date"
  FROM t
 GROUP BY  app
 ORDER BY "App"

Demo

Thanks @MTO for the demo