Just guessing here, but if you're looking for the minimum value of no where
no is just numeric, then this'll work in 10g

select t1.* from test t1 where t1.no =
(select to_char(min(cast(t2.no as number)))
from test t2
where REGEXP_LIKE (t2.no,'[[:digit:]]') )


Michael Peel


-----Original Message-----
From: Roger TEDMAN
[mailto:oracledba-ezmlmshield-x92131885.[Email address protected]
Sent: 31 March 2008 14:50
To: LazyDBA Discussion
Subject: RE: is there any way to find the minimum value from non numeric c
olumn in Oracle?


Sort out just what "minimum" means for you. With a mix of numeric and
non-numeric data, you are heading for trouble.

Min of numbers in char data is problematic in itself. Don't forget that
"<space>1" is less than "<space>9", less that "01", less than "10", and less
than "9"






> Message du 31/03/08 15:32
> De : "Michael Peel "
> A : "LazyDBA Discussion"
> Copie à :
> Objet : RE: is there any way to find the minimum value from non numeric c
olumn in Oracle?
>
> Hi,
>
> Oracle is returning the min value for the column grouped by the name.
> It's sounds like it's just not the row you want.
> You'll need to define what you mean by minimum.
>
> If you want the minimum value for the whole table, then don't group.
>
> Michael Peel
>
>
> -----Original Message-----
> From: Pitchai Siva (GE Infra Energy Non-GE)
> [mailto:oracledba-ezmlmshield-x29369014.[Email address protected]
> Sent: 31 March 2008 14:07
> 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
>
>
> **********************************************************************
> This e-mail is confidential and may be privileged.
> It may only be read, copied and used by the intended recipient.
> If you have received it in error please contact the sender immediately
> by return e-mail. Please then delete the e-mail and any copies of it
> and do not use or disclose its contents to any person.
> **********************************************************************
> Registered Office: Farncombe House, Broadway, Worcestershire, WR12 7LJ
>
> AccuRead Limited Registered Number: 3076187 England
> GSL UK Limited Registered Number: 3333860 England
> Global Solutions Limited Registered Number: 3189802 England
> **********************************************************************
>
>
>
> ---------------------------------------------------------------------
> 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
>
>
>
>


---------------------------------------------------------------------
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


**********************************************************************
This e-mail is confidential and may be privileged.
It may only be read, copied and used by the intended recipient.
If you have received it in error please contact the sender immediately
by return e-mail. Please then delete the e-mail and any copies of it
and do not use or disclose its contents to any person.
**********************************************************************
Registered Office: Farncombe House, Broadway, Worcestershire, WR12 7LJ

AccuRead Limited Registered Number: 3076187 England
GSL UK Limited Registered Number: 3333860 England
Global Solutions Limited Registered Number: 3189802 England
**********************************************************************


Oracle LazyDBA home page