Oracle SQL Query to get distinct result using like

Arulraj Joseph 0
Viewed 59
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
oracle sql
3 Answers

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

Mahamoutou0

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)

p3consulting0

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);
Gnqz0
Related