Home » SQL & PL/SQL » SQL & PL/SQL » get max value.
get max value. [message #257405] Wed, 08 August 2007 05:07 Go to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi,

Table having one character type field (DES) which is having numeric and alphanumeric value. Now i want to get the max value over alphanumeric and numeric values.

My table detail is as:-

desc tmp11
NUM, DES, QTY

data is as
1,a0123,
1,b0123,
1,0123,
1,123,


i wrote query like this

select num,max(des) from 
(select num,des,case when des> 'a0'  then 'nonnumber' else 'numaric' end as datatype from tmp11)
group by num,datatype


except to union option plz suggest me some thing else.

--Yash

Re: get max value. [message #257407 is a reply to message #257405] Wed, 08 August 2007 05:11 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
What version?

MHE
Re: get max value. [message #257413 is a reply to message #257405] Wed, 08 August 2007 05:26 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
9.2.0.6
Re: get max value. [message #257416 is a reply to message #257413] Wed, 08 August 2007 05:37 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
That's a pity. No regular expressions. I'd write a small "isnumber" function like you find here.

My script:
CREATE OR REPLACE FUNCTION is_number(piv_string IN VARCHAR2)
RETURN VARCHAR2
IS
	v_num NUMBER;
BEGIN
	v_num := TO_NUMBER(piv_string);
	RETURN 'Y';
EXCEPTION
	WHEN VALUE_ERROR THEN
		RETURN 'N';
END is_number;
/
sho err
	

WITH yourtable AS
(
  SELECT 'a0123' des FROM dual UNION ALL
  SELECT 'b0123' des FROM dual UNION ALL
  SELECT '0123'  des FROM dual UNION ALL
  SELECT '123'   des FROM dual
)
SELECT max(des)
FROM   yourtable
GROUP BY is_number(des)
/

DROP FUNCTION is_number
/


Is this what you're after?

MHE
Re: get max value. [message #257421 is a reply to message #257405] Wed, 08 August 2007 05:45 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
In the absence of 10g and its regular expression functionality, you would have to use something like:

case when translate(des, 'a0123456789', 'a') is null then 'Numeric' else 'Alphanumeric' end as datatype
Previous Topic: Insert-select query problem..
Next Topic: Finding database objects
Goto Forum:
  


Current Time: Sat Dec 03 05:37:08 CST 2016

Total time taken to generate the page: 0.19027 seconds