Oracle SQL Query to get distinct result using like-CodePudding

ID	VERSION_ID
111	ABC1234.1
222	ABC1234.2
333	ABC12345.1
444	ABC12345.2
555	ABC123456.1
666	ABC123457.1

I have an oracle table in the above structure. I want to get unique result with version_id column like 'ABC%.%. For example for version_id ABC1234.* I should get result with Id 111(least one).

The final Result should be in below format.

ID	VERSION_ID
111	ABC1234.1
333	ABC12345.1
555	ABC123456.1
666	ABC123457.1

CodePudding user response：

You could use the solution below for that purpose.

WITH YourSample (ID, VERSION_ID) AS 
(
  SELECT 111, 'ABC1234.1'   FROM dual UNION ALL
  SELECT 222, 'ABC1234.2'   FROM dual UNION ALL
  SELECT 333, 'ABC12345.1'  FROM dual UNION ALL
  SELECT 444, 'ABC12345.2'  FROM dual UNION ALL
  SELECT 555, 'ABC123456.1' FROM dual UNION ALL
  SELECT 666, 'ABC123457.1' FROM dual
)
SELECT 
    MIN(ID)KEEP(dense_rank FIRST ORDER BY ID) ID
  , MIN(VERSION_ID)KEEP(dense_rank FIRST ORDER BY ID) VERSION_ID
FROM YourSample t
GROUP BY SUBSTR( VERSION_ID, 1, INSTR(VERSION_ID, '.')-1 )
ORDER BY ID;

demo

CodePudding user response：

What I understood from the question: the rows where version_id is the MIN( version_id ) among the rows where version_id LIKE 'ABC%.%' and grouping by SUBSTR(version_id,1, INSTR(version_id,'.',-1) - 1)

CodePudding user response：

This should work:

WITH dat AS 
(
  SELECT 111 AS ID, 'ABC1234.1' AS VERSION_ID   FROM dual UNION ALL
  SELECT 222, 'ABC1234.2'   FROM dual UNION ALL
  SELECT 333, 'ABC12345.1'  FROM dual UNION ALL
  SELECT 444, 'ABC12345.2'  FROM dual UNION ALL
  SELECT 555, 'ABC123456.1' FROM dual UNION ALL
  SELECT 666, 'ABC123457.1' FROM dual
)
SELECT MIN(ID),REGEXP_REPLACE(VERSION_ID,'\.[0-9] $','')
  FROM dat
 GROUP BY REGEXP_REPLACE(VERSION_ID,'\.[0-9] $','')
 ORDER BY MIN(ID);