This would give you the minimum no grouped by name for all numeric
values
of no. However, this is a kludge. The real problem is the design where
you
have alphanumeric data in one column and you want to treat some of them
as
pure numerals.


SELECT name, MIN(no)
FROM TEST1
WHERE
TRIM(REPLACE(TRIM(TRANSLATE(name,'0123456789','0000000000')),'0',' '))
IS NOT NULL
GROUP BY name
;

-----Original Message-----
From: Pitchai Siva (GE Infra Energy Non-GE)
[mailto:oracledba-ezmlmshield-x29369014.[Email address protected]
Sent: Monday, March 31, 2008 9:07 AM
To: LazyDBA Discussion
Subject: is there any way to find the minimum value from non numeric
column in Oracle?

Hi all,



I am facing one problem in oracle



That is I have a table (test11) it has two columns (no,name), both
columns has to be defined as the varchar2 data type.

The column No having numeric data as well as character data, I want to
fetch the minimum value of NO from that table, for that I have written
the below query but its not returned the correct record



Select min (no), name from test11 group by name



So please suggest me that how can retrieve the minimum value of NO from
that table?



Thanks and Regards,

Siva.P

Bangalore







---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



Oracle LazyDBA home page