Order by problem

Hi,
How can I order the column in the correct order if my numbers are in string
fields? I am now getting
1
11
10
etc...
Regards,you could always convert/CAST the field in to numerics and sort on this
column?
"EDom" <technical@.peoplewareindia.com> wrote in message
news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can I order the column in the correct order if my numbers are in
string
> fields? I am now getting
> 1
> 11
> 10
> etc...
> Regards,
>
>|||Hi
CREATE TABLE #Test
(
col VARCHAR(10)
)
INSERT INTO #Test VALUES ('1')
INSERT INTO #Test VALUES ('11')
INSERT INTO #Test VALUES ('10')
SELECT * FROM #Test ORDER BY col ASC
"EDom" <technical@.peoplewareindia.com> wrote in message
news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can I order the column in the correct order if my numbers are in
> string
> fields? I am now getting
> 1
> 11
> 10
> etc...
> Regards,
>
>|||I f you are sure that only numeric data is inserted you could cast this
as follow to an INT (or any other numeric type)
Create Table #temp
(
Col varchar(10)
)
INSERt INTO #Temp
Select '1'
INSERt INTO #Temp
Select '11'
INSERt INTO #Temp
Select '10'
Select * from #Temp order by col
Select * from #Temp order by CAST(col AS INT)
Drop table #temp
HTH, Jens Suessmeyer.|||I f you are sure that only numeric data is inserted you could cast this
as follow to an INT (or any other numeric type)
Create Table #temp
(
Col varchar(10)
)
INSERt INTO #Temp
Select '1'
INSERt INTO #Temp
Select '11'
INSERt INTO #Temp
Select '10'
Select * from #Temp order by col
Select * from #Temp order by CAST(col AS INT)
Drop table #temp
HTH, Jens Suessmeyer.|||Ricky
Yes , but what if he has a literal characters in the column as well. CAST
conversion will fail
"Ricky" <MSN.MSN.com> wrote in message
news:uzx3lXpwFHA.2880@.TK2MSFTNGP10.phx.gbl...
> you could always convert/CAST the field in to numerics and sort on this
> column?
> "EDom" <technical@.peoplewareindia.com> wrote in message
> news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> How can I order the column in the correct order if my numbers are in
> string
>> fields? I am now getting
>> 1
>> 11
>> 10
>> etc...
>> Regards,
>>
>|||Hi,
I do have char attached to the numbers.
A1, A11, A10, A11B, A21C
etc
Regards,
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eK7ROcpwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> Ricky
> Yes , but what if he has a literal characters in the column as well. CAST
> conversion will fail
>
> "Ricky" <MSN.MSN.com> wrote in message
> news:uzx3lXpwFHA.2880@.TK2MSFTNGP10.phx.gbl...
> > you could always convert/CAST the field in to numerics and sort on this
> > column?
> >
> > "EDom" <technical@.peoplewareindia.com> wrote in message
> > news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
> >> Hi,
> >>
> >> How can I order the column in the correct order if my numbers are in
> > string
> >> fields? I am now getting
> >> 1
> >> 11
> >> 10
> >> etc...
> >>
> >> Regards,
> >>
> >>
> >>
> >
> >
>|||Good Point Uri!!!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eK7ROcpwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> Ricky
> Yes , but what if he has a literal characters in the column as well. CAST
> conversion will fail
>
> "Ricky" <MSN.MSN.com> wrote in message
> news:uzx3lXpwFHA.2880@.TK2MSFTNGP10.phx.gbl...
> > you could always convert/CAST the field in to numerics and sort on this
> > column?
> >
> > "EDom" <technical@.peoplewareindia.com> wrote in message
> > news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
> >> Hi,
> >>
> >> How can I order the column in the correct order if my numbers are in
> > string
> >> fields? I am now getting
> >> 1
> >> 11
> >> 10
> >> etc...
> >>
> >> Regards,
> >>
> >>
> >>
> >
> >
>|||Hi,
I dont get it correct
1A
11A
10A
this gives me the same result even I do sorting.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#YowWYpwFHA.664@.tk2msftngp13.phx.gbl...
> Hi
> CREATE TABLE #Test
> (
> col VARCHAR(10)
> )
> INSERT INTO #Test VALUES ('1')
> INSERT INTO #Test VALUES ('11')
> INSERT INTO #Test VALUES ('10')
> SELECT * FROM #Test ORDER BY col ASC
>
> "EDom" <technical@.peoplewareindia.com> wrote in message
> news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > How can I order the column in the correct order if my numbers are in
> > string
> > fields? I am now getting
> > 1
> > 11
> > 10
> > etc...
> >
> > Regards,
> >
> >
> >
>|||Hi
SELECT * FROM #Test ORDER BY right('0000'+col,4) ASC
"EDom" <technical@.peoplewareindia.com> wrote in message
news:eFapS40wFHA.2656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I dont get it correct
> 1A
> 11A
> 10A
> this gives me the same result even I do sorting.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#YowWYpwFHA.664@.tk2msftngp13.phx.gbl...
>> Hi
>> CREATE TABLE #Test
>> (
>> col VARCHAR(10)
>> )
>> INSERT INTO #Test VALUES ('1')
>> INSERT INTO #Test VALUES ('11')
>> INSERT INTO #Test VALUES ('10')
>> SELECT * FROM #Test ORDER BY col ASC
>>
>> "EDom" <technical@.peoplewareindia.com> wrote in message
>> news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
>> > Hi,
>> >
>> > How can I order the column in the correct order if my numbers are in
>> > string
>> > fields? I am now getting
>> > 1
>> > 11
>> > 10
>> > etc...
>> >
>> > Regards,
>> >
>> >
>> >
>>
>