CODEWITHSUNDEEP
sqloracle-database
Sanjeev Ajay
Sanjeev Ajay

Reputation: 5

Get Maximum sequence for column value is number only

SELECT * FROM TABLE;

CUSTNUM  SEQ    SECTION   
--------------------
220684     5    12c
213567     4    33
220684     3    25
213567     6    10
213567     2    a12
220684     1    30

Require oracle 10g query for output as mentioned below. Need to get the maximum sequence number for Customer number whose section column value is only number not having any digit.

  SQL> SELECT CUSTNUM,MAX(SEQ) FROM TABLE WHERE REGEXP_LIKE(SECTION, 
  '^[[:digit:]]+$') BETWEEN 22 AND 50;  ---  how to use between in regex

  CUSTNUM  SEQ    
  -------------------- 
  220684    3   
  213567    4

Upvotes: 0

Views: 269

Answers (1)

MT0
MT0

Reputation: 167972

You need to test if the SECTION is a number in an inner query and then, having filtered out the non-numbers, you can filter the number range in the outer query:

SELECT CUSTNUM,
       MAX(SEQ)
FROM   (
  SELECT CUSTNUM,
         SEQ,
         SECTION
  FROM   TABLE
  WHERE  REGEXP_LIKE(SECTION, '^[[:digit:]]+$')
)
WHERE  TO_NUMBER( SECTION ) BETWEEN 22 AND 50
GROUP BY CUSTNUM;

If you try doing both filters in the same sub-query then it is possible that TO_NUMBER() will be evaluated before the REGEXP_LIKE() and the query will raise an exception for a non-numeric row.

Upvotes: 1

Related Questions