'How to query the latest version for each app?

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.

sqloracle


Solution 1:[1]

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;

Solution 2:[2]

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

db<>fiddle here

Solution 3:[3]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3

Related Questions