Home » SQL & PL/SQL » SQL & PL/SQL » Maximum value
Maximum value [message #352876] Fri, 10 October 2008 00:23 Go to next message
gaikwadrachit
Messages: 33
Registered: June 2007
Location: mumbai
Member
Hi
i am having a table project which has two columns
Project_version and project_name,both has varchar data type

my query is that i want to find maximum value of column project version.
the data in this column is like
9.6
10.7
10.1.3
9.67

select max (to_number(project_ver)) from project;gives
9.67
which is wrong

as the maximum value from this data is 10.7
can you give give the exact query to get maximum value

[Updated on: Fri, 10 October 2008 00:32]

Report message to a moderator

Re: Maximum value [message #352877 is a reply to message #352876] Fri, 10 October 2008 00:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why is it 10.1.3 and not 10.7?

Regards
Michel
Re: Maximum value [message #352878 is a reply to message #352877] Fri, 10 October 2008 00:32 Go to previous messageGo to next message
gaikwadrachit
Messages: 33
Registered: June 2007
Location: mumbai
Member
sorry it is 10.7
Re: Maximum value [message #352880 is a reply to message #352876] Fri, 10 October 2008 00:36 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

No straight solution ... I think

Try to implement the following logic , by using a function or something.

select Max ( x) from  ( 
select '09.06.00' x from dual  UNION 
select '10.07.00' from dual UNION 
select '10.01.03' from dual UNION
select '09.67.00' from dual
 )


Thumbs Up
Rajuvan.
Re: Maximum value [message #352882 is a reply to message #352880] Fri, 10 October 2008 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select Max ( x) from  ( 
  2  select '09.06.00' x from dual  UNION 
  3  select '10.07.00' from dual UNION 
  4  select '10.01.03' from dual UNION
  5  select '09.67.00' from dual
  6   )
  7  /
MAX(X)
--------
10.07.00

1 row selected.

OP said the result should be 10.1.3

Regards
Michel
Re: Maximum value [message #352883 is a reply to message #352882] Fri, 10 October 2008 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Aaargh! OP changed his post afterward.

This question has already been asked, please search for it but Rajuvan showed you the way.

Regards
Michel
Re: Maximum value [message #352884 is a reply to message #352876] Fri, 10 October 2008 00:40 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

No . OP Said ..


Quote:
sorry it is 10.7


Thumbs Up
Rajuvan.

[Updated on: Fri, 10 October 2008 00:42]

Report message to a moderator

Re: Maximum value [message #352916 is a reply to message #352876] Fri, 10 October 2008 04:24 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
@rajavu1

You provided examples other than the OP's (no '0' at the beginning).


@gaikwadrachit

Your query is very simple using LPAD:
You can select the first row (rownum = 1) while ordering your result by LPAD(<your_column>,<length_of_your_column>)

Rajy

Re: Maximum value [message #352920 is a reply to message #352916] Fri, 10 October 2008 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What Rajuvan posted is NOT a solution, it is a clue to how to do it.

Your LPAD "solution" does not work for the general case. Add 10.11 row and you will see.

What does the following means:
Quote:
You can select the first row (rownum = 1) while ordering your result by LPAD(<your_column>,<length_of_your_column>)



Regards
Michel
Re: Maximum value [message #352921 is a reply to message #352876] Fri, 10 October 2008 04:37 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

And order by what ?

Quote:
You can select the first row (rownum = 1) while ordering your result by LPAD(<your_column>,<length_of_your_column>)


please explain.

Thumbs Up
Rajuvan

[Updated on: Fri, 10 October 2008 04:38]

Report message to a moderator

Re: Maximum value [message #352926 is a reply to message #352920] Fri, 10 October 2008 04:48 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
Sorry I didn't note that before!
You're right.
Thank you for your notice!!
Re: Maximum value [message #352929 is a reply to message #352926] Fri, 10 October 2008 04:57 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
The idea with LPAD ist good, something like this could do the job:
select x,lpad(x,5+length(x)-instr(x,'.'),'0') ordt from
  ( 
  select '6.06' x from dual UNION 
  select '19.07.00'  from dual UNION 
  select '11.01.0388'  from dual UNION
  select '10.11.03'  from dual UNION
  select '9.67.00'   from dual UNION
  select '7.01.689'   from dual 
 )
order by 2

gives:
X	        ORDT

6.06    	0006.06
7.01.689	0007.01.689
9.67.00	        0009.67.00
10.11.03	0010.11.03
11.01.0388	0011.01.0388
19.07.00	0019.07.00
Re: Maximum value [message #352933 is a reply to message #352929] Fri, 10 October 2008 05:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cheat with 19.07, it is 19.7
The hint to achieve the whole problem was already given.
And the solution also, if you just search it.

Regards
Michel
Re: Maximum value [message #352936 is a reply to message #352933] Fri, 10 October 2008 05:10 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
@Michel, what cheat, don't uderstand Your answer ? 19.7 is sorted well ..
select x,lpad(x,5+length(x)-instr(x,'.'),'0') ordt from
  ( 
  select '6.06' x     rom dual UNION 
  select '19.07.00'   from dual UNION
  select '19.7'       from dual UNION 
  select '11.01.0388' from dual UNION
  select '10.11.03'   from dual UNION
  select '9.67.00'    from dual UNION
  select '7.01.689'   from dual 
 )
order by 2

gives
X	ORDT

6.06	0006.06
7.01.689	0007.01.689
9.67.00	        0009.67.00
10.11.03	0010.11.03
11.01.0388	0011.01.0388
19.07.00	0019.07.00
19.7	        0019.7


Re: Maximum value [message #352939 is a reply to message #352936] Fri, 10 October 2008 05:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
add 19.10
Re: Maximum value [message #352941 is a reply to message #352939] Fri, 10 October 2008 05:25 Go to previous message
_jum
Messages: 509
Registered: February 2008
Senior Member
of course, I see Embarassed
Previous Topic: ORA-00054: resource busy and acquire with NOWAIT specified
Next Topic: not Load PL/SQL in PROC
Goto Forum:
  


Current Time: Wed Dec 07 16:26:48 CST 2016

Total time taken to generate the page: 0.05844 seconds