Need a Query ! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need a Query !

Author

Topic

sqlilliterate
Starting Member

40 Posts

Posted - 2008-07-18 : 02:52:56

I've the following values in a column(col1) in my table

1.0
1.1
0.12
1.12
1.9
1.10
2.1
2.10
2.12
2.2

If i use select max(col1) from <table>
am getting 2.2 as the max value. But I want 2.12 to be selected, which is the max value after the decimal point...

Your query to make this work would immensely help me.

thanks...

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-18 : 02:54:06

what datatype is your column?

Em

sqlilliterate
Starting Member

40 Posts

Posted - 2008-07-18 : 02:58:37

datatype is Varchar(10)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-18 : 03:01:14

max(cast(right(col1,charindex('.',col1,1)) as int))

Em

charul
Starting Member

18 Posts

Posted - 2008-07-18 : 03:17:02

quote:
Originally posted by sqlilliterate

I've the following values in a column(col1) in my table

1.0
1.1
0.12
1.12
1.9
1.10
2.1
2.10
2.12
2.2

If i use select max(col1) from <table>
am getting 2.2 as the max value. But I want 2.12 to be selected, which is the max value after the decimal point...

Your query to make this work would immensely help me.

thanks...

Select A,CAST([NO] as Int)
from
(
Select A, [NO]= SUBSTRING(cast(A as varchar),CHARINDEX('.',A,1)+1, LEN(A))from
(
Select 1.10 as [A]
UNION
Select 1.11
UNION
Select 1.21
UNION
Select .13
UNION
Select 2.0
)X )Y where
CAST([NO] as Int)=
(Select MAX(CAST([NO] as Int))
from
(
Select A, [NO]= SUBSTRING(cast(A as varchar),CHARINDEX('.',A,1)+1, LEN(A))from
(
Select 1.10 as [A]
UNION
Select 1.11
UNION
Select 1.21
UNION
Select .13
UNION
Select 2.0
)X )Y )

regds
CA

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-18 : 03:20:42

dude, don't you think that's just slightly over-complicating it?

quote:
Originally posted by charul

quote:
Originally posted by sqlilliterate

I've the following values in a column(col1) in my table

1.0
1.1
0.12
1.12
1.9
1.10
2.1
2.10
2.12
2.2

If i use select max(col1) from <table>
am getting 2.2 as the max value. But I want 2.12 to be selected, which is the max value after the decimal point...

Your query to make this work would immensely help me.

thanks...

Select A,CAST([NO] as Int)
from
(
Select A, [NO]= SUBSTRING(cast(A as varchar),CHARINDEX('.',A,1)+1, LEN(A))from
(
Select 1.10 as [A]
UNION
Select 1.11
UNION
Select 1.21
UNION
Select .13
UNION
Select 2.0
)X )Y where
CAST([NO] as Int)=
(Select MAX(CAST([NO] as Int))
from
(
Select A, [NO]= SUBSTRING(cast(A as varchar),CHARINDEX('.',A,1)+1, LEN(A))from
(
Select 1.10 as [A]
UNION
Select 1.11
UNION
Select 1.21
UNION
Select .13
UNION
Select 2.0
)X )Y )

regds
CA

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-18 : 03:35:14

declare @t table (col1 varchar(10))
insert into @t
select '1.0'
union all select '1.1'
union all select'0.12'
union all select'1.12'
union all select'1.9'
union all select'1.10'
union all select'2.1'
union all select'2.10'
union all select'2.12'
union all select'2.2'

select MAX(cast(substring(col1,charindex('.',col1)+1,LEN(col1)) AS INT))
from @t

Em

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-18 : 04:08:33

Select top 1 col from your_table
order by parsename(col,2) desc,parsename(col,1)*1 desc

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-18 : 04:12:11

I went for a meeting and didnt see the replies

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources