order by values in IN clause

Hi All,
I have a problem [stated below].
select '$'+CONVERT(VARCHAR,SUM(mMoney),1) from trans where TS IN
('2001','23456789') GROUP BY TS
select '$'+CONVERT(VARCHAR,SUM(mMoney),1) from trans where TS IN
('23456789','2001') GROUP BY TS
both return same result set.
$99,200.00
$4,343.00
I need to order the result set by the values that I give in the IN
clause. Is this possible?Anybody could please explain why this is
happening and what would be the remedy.
Thanks in advance.
Thanks & Regards,
Shankar.> I need to order the result set by the values that I give in the IN
> clause. Is this possible?Anybody could please explain why this is
> happening and what would be the remedy.
SQL Server is free to return results in any order unless you specify ORDER
BY. This is regardless of the order of values in your IN clause, order of
joined tables, table indexes, etc.
One solution is to add an additional value for the desired sequencing so
that you can specify ORDER BY. The example below uses a derived table:
SELECT '$'+CONVERT(VARCHAR,SUM(mMoney),1)
FROM trans
JOIN (SELECT '2001' AS TS, 1 AS Seq
UNION ALL SELECT '23456789', 2) AS trans_list
ON trans.TS = trans_list.TS
GROUP BY trans_list.TS, trans_list.Seq
ORDER BY trans_list.Seq
BTW, formatting data for display purposes is a job best done in the
presentation layer rather than in the database. That approach is more
scalable. Also, front-end tools (e.g. Reporting Services) provide much
richer formatting capability than Transact-SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
<shankararaman.s@.gmail.com> wrote in message
news:1146824029.416992.301160@.j73g2000cwa.googlegroups.com...
> Hi All,
> I have a problem [stated below].
> select '$'+CONVERT(VARCHAR,SUM(mMoney),1) from trans where TS IN
> ('2001','23456789') GROUP BY TS
> select '$'+CONVERT(VARCHAR,SUM(mMoney),1) from trans where TS IN
> ('23456789','2001') GROUP BY TS
> both return same result set.
> $99,200.00
> $4,343.00
> I need to order the result set by the values that I give in the IN
> clause. Is this possible?Anybody could please explain why this is
> happening and what would be the remedy.
>
> Thanks in advance.
> Thanks & Regards,
> Shankar.
>|||try this.
select '$'+CONVERT(VARCHAR,SUM(mMoney),1) from trans where TS IN
('23456789','2001') GROUP BY TS
order by TS|||Also format the data in your front end application
Madhivanansql

ORDER BY specific values

Hi Everyone,
I was wondering if there is anyway to ORDER BY in a query by certain values first. I have a table with Projects and subprojects and sub-subprojects and I always want to display the parent project first. Is there anyway that I can do that.
Thanks for all your help.Could you give us a little inside information about your data-structure, eventually accompanied with some sample data and the way you would like to have it ?sql

Order By Question

Hi,
I have a table with a field i sort on which has values like: p100-01,
p101-02, p999-05, p1000-03. On my order by shows these values like this:
p100-01
p1000-03
p101-02
p999-05
Is there a way around this or since its alpha numeric I am stuck with this?
ThanksYou can create a column that pads the strings out the way you want them
and do an ORDER BY it.
CASE WHEN foo_nbr LIKE 'p[0-9][0-9][0-9]-[0-9][0-9]'
THEN SUBSTRING (foo-nbr, 1,4) + '0' + SUBSTRING (foo-nbr,
5,7)
ELSE foo_nbr END AS sort_col|||if your pattern is always as illustrated (i.e., single
char+number+dash+number) then try
order by convert(decimal(30,5), replace(substring(value,2,9), '-', '.'))
you'll want to adjust the decimal precision and scale based on how many
numbers you'll have before and after the dash, if it's constant or at
least has a specified range.
also, this will not be a fast order, except on a small result set
Kyle wrote:
> Hi,
> I have a table with a field i sort on which has values like: p100-01,
> p101-02, p999-05, p1000-03. On my order by shows these values like this:
> p100-01
> p1000-03
> p101-02
> p999-05
> Is there a way around this or since its alpha numeric I am stuck with this
?
> Thanks
>|||... ["value" in this represents the column name] ...
Trey Walpole wrote:
> if your pattern is always as illustrated (i.e., single
> char+number+dash+number) then try
> order by convert(decimal(30,5), replace(substring(value,2,9), '-', '.'))
> you'll want to adjust the decimal precision and scale based on how many
> numbers you'll have before and after the dash, if it's constant or at
> least has a specified range.
> also, this will not be a fast order, except on a small result set
> Kyle wrote:
>|||On Fri, 9 Sep 2005 13:41:47 -0600, Kyle wrote:

>Hi,
>I have a table with a field i sort on which has values like: p100-01,
>p101-02, p999-05, p1000-03. On my order by shows these values like this:
>p100-01
>p1000-03
>p101-02
>p999-05
>Is there a way around this or since its alpha numeric I am stuck with this?
>Thanks
>
Hi Kyle,
This requirement indicates that the value is not atomic, but that the
numeric parts have different meaning. Instead of using a kludge to fix
the sorting, it's probably better to split the various parts of this
data into seperate columns, and concatenate them when selecting the
data.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I developed a query to do natural alphanumeric sorting. This query breaks
the alphanumeric string up into chunks of char's and number's, then orders b
y
the chunks. It only works for the first 4 chunks. I haven't had the time,
but I am sure that you could build a trigger to populate a,b,c and d columns
and sort by that.
Archer
select TOP 100 fieldid as 'sortcolumn',
LEFT(fieldid, PATINDEX('%[0-9]%', fieldid) - 1) as 'a',
CASE
WHEN right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ) not
like '%[a-z,-]%'
then CAST(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1
) AS INT)
else
cast (
left (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
case
when patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 )) = 0
then patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 ))
else patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 )) -1
end
)
as int)
end
as 'b',
LEFT (
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
,
CASE
WHEN PATINDEX('%[0-9]%',
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ))
- patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) = 0
THEN
LEN( right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ))
- patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
)
ELSE PATINDEX('%[0-9]%',
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ))
- patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) -1
END
)
as 'c',
CASE
WHEN right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ) not like '%[a-z,-]%'
then CAST(right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ) AS INT)
else
cast (
left (
right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ),
case
when patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 )) = 0
then patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ))
else patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 )) -1
end
)
as int)
end
as 'd',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
) as 'cTEST'
from tblsamplesTEST
order by a,b,c,d
"Kyle" wrote:

> Hi,
> I have a table with a field i sort on which has values like: p100-01,
> p101-02, p999-05, p1000-03. On my order by shows these values like this:
> p100-01
> p1000-03
> p101-02
> p999-05
> Is there a way around this or since its alpha numeric I am stuck with this
?
> Thanks
>
>|||Kyle wrote:
> I have a table with a field i sort on which has values like: p100-01,
> p101-02, p999-05, p1000-03. On my order by shows these values like this:
> p100-01
> p1000-03
> p101-02
> p999-05
> Is there a way around this or since its alpha numeric I am stuck with this?[/color
]
If only the first number has a variable length, but the second number is
always two digits:
order by len(i), i
Dieter

Order by Clause

Hi,
I have column with values January, February and so on. I need to perform
sort based on months instead the system sorts it by Alphabetical Order. Any
Hint?
Thanks
MannyManny Chohan wrote:
> Hi,
> I have column with values January, February and so on. I need to perform
> sort based on months instead the system sorts it by Alphabetical Order. An
y
> Hint?
> Thanks
> Manny
How about storing dates as DATETIME / SMALLDATETIME rather than
strings? If it's too late to do that then you can try:
SELECT mth
FROM tbl
ORDER BY CONVERT(DATETIME,mth+' 01 2000',1) ;
David Portas
SQL Server MVP
--|||do you not have a real date? if not, then do you at least have a year as
well? if not, sorting my month number is pretty meaningless. (e.g. Jan
05 comes after Dec 04).
Manny Chohan wrote:
> Hi,
> I have column with values January, February and so on. I need to perform
> sort based on months instead the system sorts it by Alphabetical Order. An
y
> Hint?
> Thanks
> Manny|||Manny Chohan wrote:

> I have column with values January, February and so on. I need to
> perform sort based on months instead the system sorts it by
> Alphabetical Order. Any Hint?
Create a table with months:
Table Months
ID int NOT NULL,
MonthName varchar(20)
and do a join on that. Normally it would be better to save the month
ID instead of the full text.
HTH,
Stijn Verrept.|||... order by
charindex(monthnamecol+'*','January*Febr
uary*March*April*May*June*July*Augus
t*September*October*November*December*')
Steve Kass
Drew University
Manny Chohan wrote:

>Hi,
>I have column with values January, February and so on. I need to perform
>sort based on months instead the system sorts it by Alphabetical Order. Any
>Hint?
>Thanks
>Manny
>|||Thanks. It worked.
"Steve Kass" wrote:

> ... order by
> charindex(monthnamecol+'*','January*Febr
uary*March*April*May*June*July*Aug
ust*September*October*November*December*
')
> Steve Kass
> Drew University
> Manny Chohan wrote:
>
>|||I apologize for the off-topic comment, but this one is just too good to pass
:

> do you not have a real date?
How many times have I been asked that question - not necesarily in the same
context, but still... :)
ML
http://milambda.blogspot.com/|||David with all due respect, I'd suggest a datetime format that carries the
century. Yes, you specify the year 2000 but we should all be developing cod
e
that doesn't leave ambiguity because you never know what SQL Server defaults
will be in the future and more impportantly how your technique will be
applied to other situations.
CONVERT(DATETIME,mth+' 01 2000',101) --mm/dd/yyyy format
And, we should be thinking globally so it really should be:
CONVERT(DATETIME,mth+' 01 2000',112) --yyyymmdd format
Just my two cents,
Joe
"David Portas" wrote:

> Manny Chohan wrote:
> How about storing dates as DATETIME / SMALLDATETIME rather than
> strings? If it's too late to do that then you can try:
> SELECT mth
> FROM tbl
> ORDER BY CONVERT(DATETIME,mth+' 01 2000',1) ;
> --
> David Portas
> SQL Server MVP
> --
>|||Joe from WI wrote:
> David with all due respect, I'd suggest a datetime format that carries the
> century. Yes, you specify the year 2000 but we should all be developing c
ode
> that doesn't leave ambiguity because you never know what SQL Server defaul
ts
> will be in the future and more impportantly how your technique will be
> applied to other situations.
> CONVERT(DATETIME,mth+' 01 2000',101) --mm/dd/yyyy format
>
A good point.

> And, we should be thinking globally so it really should be:
> CONVERT(DATETIME,mth+' 01 2000',112) --yyyymmdd format
>
That does work although on the face of it the string is wrong. 112
defines the format as YYYYMMDD, which is not what you have specified.
There is some implict conversion at work here so I'd stick to the 101
version because the string that's passed complies with the documented
format and behaviour for CONVERT.
David Portas
SQL Server MVP
--sql

order by bearing in mind seconds

hi,
How could I order by datetime type including the seconds?
Now I am using order by <field> and it' getting the values of this way:
2005-08-04 03:03:42.000
2005-08-04 04:00:33.000
2005-08-04 07:31:20.000
Instead of:
2005-08-04 07:31:20.000
2005-08-04 04:00:33.000
2005-08-04 03:03:42.000
Best wishes,Hi Enric,
I'm not sure what you think is wrong with the sorting that you get. It seems
perfectly valid.
Sorting by datetime includes ALL datetime elements (even the milisecconds).
If you want to sort ONLY by seconds, ingnoring the other parts, use:
order by datepart(second, dt)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:F722D128-297D-4DD9-A8DC-05F34F714B87@.microsoft.com...
> hi,
> How could I order by datetime type including the seconds?
> Now I am using order by <field> and it' getting the values of this way:
> 2005-08-04 03:03:42.000
> 2005-08-04 04:00:33.000
> 2005-08-04 07:31:20.000
> Instead of:
> 2005-08-04 07:31:20.000
> 2005-08-04 04:00:33.000
> 2005-08-04 03:03:42.000
> Best wishes,
>
>
>
>|||I don't understand the question. The first example IS ordered by time
including the seconds. Ordering by a DATETIME column always takes
account of the whole date and time value, unless you perform some other
manipulation of the value.
David Portas
SQL Server MVP
--|||In order to accomplished you goad you presented you should just change the
sort order for that ?!
order by <col> DESC
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Enric" wrote:

> hi,
> How could I order by datetime type including the seconds?
> Now I am using order by <field> and it' getting the values of this way:
> 2005-08-04 03:03:42.000
> 2005-08-04 04:00:33.000
> 2005-08-04 07:31:20.000
> Instead of:
> 2005-08-04 07:31:20.000
> 2005-08-04 04:00:33.000
> 2005-08-04 03:03:42.000
> Best wishes,
>
>
>
>|||Thanks a lot to all,
"Jens Sü?meyer" wrote:
> In order to accomplished you goad you presented you should just change the
> sort order for that ?!
> order by <col> DESC
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Enric" wrote:
>

order by

hi i have the following
create table #station_mix
(
station varchar(100)
--several other columns
)
insert into #station_mix values('qtq')
insert into #station_mix values('nws')
insert into #station_mix values('stw')
insert into #station_mix values('tcn')
insert into #station_mix values('gtv')
when i do a select * from #station_mix i want the output to be in the order.
'tcn', 'gtv', 'qtq', 'nws','stw'
how do i do this?
Thanks
ICHORHi ichor,
since you havent got any sort crteria in the name itself, you need some
other column to do the ordering:
create table #station_mix (
station varchar(100),
order_no INT
--several other columns)
insert into #station_mix (station, order_no) values('qtq', 2)
insert into #station_mix (station, order_no) values('nws', 3)
insert into #station_mix (station, order_no) values('stw', 4)
insert into #station_mix (station, order_no) values('tcn', 0)
insert into #station_mix (station, order_no) values('gtv', 1)
select * from #station_mix order by order_no
Micha
"ichor" <ichor@.hotmail.com> schrieb im Newsbeitrag
news:OzQacBbkFHA.3380@.TK2MSFTNGP12.phx.gbl...
> hi i have the following
> create table #station_mix
> (
> station varchar(100)
> --several other columns
> )
> insert into #station_mix values('qtq')
> insert into #station_mix values('nws')
> insert into #station_mix values('stw')
> insert into #station_mix values('tcn')
> insert into #station_mix values('gtv')
> when i do a select * from #station_mix i want the output to be in the
> order.
> 'tcn', 'gtv', 'qtq', 'nws','stw'
> how do i do this?
> Thanks
> ICHOR
>
>|||Hi,
Which order you need to get the output. If it is alphabetical order why dont
you try ORDER BY clause.
Incase if you cant use order by clause then , create a CLUSTERED index on
station column
Thanks
Hari
SQL Server MVP
"ichor" <ichor@.hotmail.com> wrote in message
news:OzQacBbkFHA.3380@.TK2MSFTNGP12.phx.gbl...
> hi i have the following
> create table #station_mix
> (
> station varchar(100)
> --several other columns
> )
> insert into #station_mix values('qtq')
> insert into #station_mix values('nws')
> insert into #station_mix values('stw')
> insert into #station_mix values('tcn')
> insert into #station_mix values('gtv')
> when i do a select * from #station_mix i want the output to be in the
> order.
> 'tcn', 'gtv', 'qtq', 'nws','stw'
> how do i do this?
> Thanks
> ICHOR
>
>|||Any reason you want this order?
One way is
Select station from #station_mix
Order by case when station ='tcn' then 1 when station ='gtv' then 2
when station ='qtq' then 3 when station ='nws' then 4
when station ='stw' then 5|||ichor wrote:

> when i do a select * from #station_mix i want the output to be in the
> order.
> 'tcn', 'gtv', 'qtq', 'nws','stw'
> how do i do this?
> Thanks
> ICHOR
You could add a "Priority" column to #station_mix, then Order By the
Priority column.
create table #station_mix
(
station varchar(100),
priority int
)
insert into #station_mix values('qtq', 3)
insert into #station_mix values('nws', 4)
insert into #station_mix values('stw', 5)
insert into #station_mix values('tcn', 1)
insert into #station_mix values('gtv', 2)
select station from #station_mix order by priority
Ben|||Hi
I can't see that these are in any specific order, is there another column
that determines this order? If not try
SELECT station
FROM (
SELECT station, CASE station WHEN 'tcn' THEN 1
WHEN 'gtv' THEN 2
WHEN 'qtq' THEN 3
WHEN 'nws' THEN 4
WHEN 'stw' THEN 5
ELSE 6
END AS Orderby
FROM #station_mix ) A
ORDER BY Orderby
John
"ichor" wrote:

> hi i have the following
> create table #station_mix
> (
> station varchar(100)
> --several other columns
> )
> insert into #station_mix values('qtq')
> insert into #station_mix values('nws')
> insert into #station_mix values('stw')
> insert into #station_mix values('tcn')
> insert into #station_mix values('gtv')
> when i do a select * from #station_mix i want the output to be in the orde
r.
> 'tcn', 'gtv', 'qtq', 'nws','stw'
> how do i do this?
> Thanks
> ICHOR
>
>|||>Incase if you cant use order by clause then , create a CLUSTERED index on
>station column
Hari,
As you probably know, this will not always guarantee that the rows will be
returned in the
order of the Clustered Index.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:%23YZ$9QbkFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Which order you need to get the output. If it is alphabetical order why
> dont you try ORDER BY clause.
> Incase if you cant use order by clause then , create a CLUSTERED index on
> station column
> Thanks
> Hari
> SQL Server MVP
>
> "ichor" <ichor@.hotmail.com> wrote in message
> news:OzQacBbkFHA.3380@.TK2MSFTNGP12.phx.gbl...
>|||Ichor,
In case you didn't get the message from the other replies: the only way
to guarantee a specific output order is to use the ORDER BY clause.
In the ORDER BY clause you can specify a column name (for example if you
want the output to be sorted alphabetically) or an expression (for
example a CASE .. WHEN expression, as demonstrated in Madhivanan's
reply). This will specify the values that are used for sorting.
In addition to that, you can specify a Collation and whether the result
should be sorted ASCending or DESCending. By default, the column's
default collation is used, or the database default collation, and the
result is sorted ascending. The collation determines the sorting rules,
for example whether the ordering should be case sensitive or not.
Checkout BOL for further details.
Hope this helps,
Gert-Jan
ichor wrote:
> hi i have the following
> create table #station_mix
> (
> station varchar(100)
> --several other columns
> )
> insert into #station_mix values('qtq')
> insert into #station_mix values('nws')
> insert into #station_mix values('stw')
> insert into #station_mix values('tcn')
> insert into #station_mix values('gtv')
> when i do a select * from #station_mix i want the output to be in the orde
r.
> 'tcn', 'gtv', 'qtq', 'nws','stw'
> how do i do this?
> Thanks
> ICHOR

Order a character field numercially

I have a table with code and description columns. The code column is a CHAR
(10) with values like:
1, 101, 15, DC1, etc.
This code is displayed in a drop down list and sorts as follows because it i
s a character field:
1
101
15
DC1
I need to have this drop down sorted in a manner that takes into account the
numeric order of the code, as follows:
1
15
101
DC1
The codes are industry standard codes, so I cannot tell users to enter 001,
015, 101, etc. The codes are alphanumeric in nature. There are hundreds of
codes, which makes it difficult for a user to scroll down to find the corre
ct code. Is there a way to
sort a character field in this manner? ThanksORDER BY CASE ISNUMERIC(column) WHEN 1 THEN CONVERT(INT, column) ELSE column
END
( Except note that there are some isNumeric exceptions...
http://www.aspfaq.com/show.asp?id=2390 )
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||you could try:
ORDER BY
CASE PATINDEX('[a-z]', LEFT(YourColumn, 1))
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
This assumes that all codes with alphas begin with an alpha. If that's not
true, change it to:
ORDER BY
CASE PATINDEX('%[a-z]%', YourColumn)
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
You also may have to compensate for a case-sensitive collation setting, and
possibly other issues... But this is a start, at least.
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||Don
CREATE TABLE TEST
(
COL VARCHAR(4)
)
GO
INSERT INTO TEST VALUES ('1')
INSERT INTO TEST VALUES ('101')
INSERT INTO TEST VALUES ('15')
INSERT INTO TEST VALUES ('DC1')
GO
SELECT * FROM TEST ORDER BY RIGHT('0000'+COL,4)
GO
DROP TABLE TEST
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||Yet another solution:
ORDER BY LEN(YourColumn), YourColumn
Hope this helps,
Gert-jan
Don Jones wrote:
> I have a table with code and description columns. The code column is a CH
AR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account t
he numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter 001, 015, 1
01, etc. The codes are alphanumeric in nature. There are hundreds of codes, which
makes it difficult for a user to scroll down to find the correct code. Is there a w
ay
to sort a character field in this manner? Thanks
(Please reply only to the newsgroup)

Order a character field numercially

I have a table with code and description columns. The code column is a CHAR(10) with values like:
1, 101, 15, DC1, etc.
This code is displayed in a drop down list and sorts as follows because it is a character field:
1
101
15
DC1
I need to have this drop down sorted in a manner that takes into account the numeric order of the code, as follows:
1
15
101
DC1
The codes are industry standard codes, so I cannot tell users to enter 001, 015, 101, etc. The codes are alphanumeric in nature. There are hundreds of codes, which makes it difficult for a user to scroll down to find the correct code. Is there a way to
sort a character field in this manner? Thanks
ORDER BY CASE ISNUMERIC(column) WHEN 1 THEN CONVERT(INT, column) ELSE column
END
( Except note that there are some isNumeric exceptions...
http://www.aspfaq.com/show.asp?id=2390 )
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>
|||you could try:
ORDER BY
CASE PATINDEX('[a-z]', LEFT(YourColumn, 1))
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
This assumes that all codes with alphas begin with an alpha. If that's not
true, change it to:
ORDER BY
CASE PATINDEX('%[a-z]%', YourColumn)
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
You also may have to compensate for a case-sensitive collation setting, and
possibly other issues... But this is a start, at least.
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>
|||Don
CREATE TABLE TEST
(
COL VARCHAR(4)
)
GO
INSERT INTO TEST VALUES ('1')
INSERT INTO TEST VALUES ('101')
INSERT INTO TEST VALUES ('15')
INSERT INTO TEST VALUES ('DC1')
GO
SELECT * FROM TEST ORDER BY RIGHT('0000'+COL,4)
GO
DROP TABLE TEST
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>
|||Yet another solution:
ORDER BY LEN(YourColumn), YourColumn
Hope this helps,
Gert-jan
Don Jones wrote:
> I have a table with code and description columns. The code column is a CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter 001, 015, 101, etc. The codes are alphanumeric in nature. There are hundreds of codes, which makes it difficult for a user to scroll down to find the correct code. Is there a way
to sort a character field in this manner? Thanks
(Please reply only to the newsgroup)

Order a character field numercially

I have a table with code and description columns. The code column is a CHAR(10) with values like
1, 101, 15, DC1, etc
This code is displayed in a drop down list and sorts as follows because it is a character field
10
1
DC
I need to have this drop down sorted in a manner that takes into account the numeric order of the code, as follows
1
10
DC
The codes are industry standard codes, so I cannot tell users to enter 001, 015, 101, etc. The codes are alphanumeric in nature. There are hundreds of codes, which makes it difficult for a user to scroll down to find the correct code. Is there a way to sort a character field in this manner? ThankORDER BY CASE ISNUMERIC(column) WHEN 1 THEN CONVERT(INT, column) ELSE column
END
( Except note that there are some isNumeric exceptions...
http://www.aspfaq.com/show.asp?id=2390 )
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||you could try:
ORDER BY
CASE PATINDEX('[a-z]', LEFT(YourColumn, 1))
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
This assumes that all codes with alphas begin with an alpha. If that's not
true, change it to:
ORDER BY
CASE PATINDEX('%[a-z]%', YourColumn)
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
You also may have to compensate for a case-sensitive collation setting, and
possibly other issues... But this is a start, at least.
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||Don
CREATE TABLE TEST
(
COL VARCHAR(4)
)
GO
INSERT INTO TEST VALUES ('1')
INSERT INTO TEST VALUES ('101')
INSERT INTO TEST VALUES ('15')
INSERT INTO TEST VALUES ('DC1')
GO
SELECT * FROM TEST ORDER BY RIGHT('0000'+COL,4)
GO
DROP TABLE TEST
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||Yet another solution:
ORDER BY LEN(YourColumn), YourColumn
Hope this helps,
Gert-jan
Don Jones wrote:
> I have a table with code and description columns. The code column is a CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter 001, 015, 101, etc. The codes are alphanumeric in nature. There are hundreds of codes, which makes it difficult for a user to scroll down to find the correct code. Is there a way to sort a character field in this manner? Thanks
--
(Please reply only to the newsgroup)

ORDER - varchar column contains Char & int values

Hi,
I have a table with a Varchar column(col1) contains values as below,
Low
Upper
4
Lp
31
1
2
Jack
3
default order/order by col1 is
1
2
3
31
4
Jack
Low
Lp
Upper
I want to order this column in such a way that
blank spaces first, numbers second(in ascending) and alphabet...
1
2
3
4
31
Jack
Low
Lp
Upper
Please advise.
Thanks,
Soura
USE tempdb
GO
CREATE TABLE a (cola varchar(10))
GO
INSERT INTO a VALUES('Low')
INSERT INTO a VALUES('Upper')
INSERT INTO a VALUES('4')
INSERT INTO a VALUES('Lp')
INSERT INTO a VALUES('31')
INSERT INTO a VALUES('1')
INSERT INTO a VALUES('2')
INSERT INTO a VALUES('Jack')
INSERT INTO a VALUES('3')
GO
SELECT cola,
CASE
WHEN ISNUMERIC(cola) = 1 THEN CAST(cola AS bigint)
ELSE 9223372036854775807 -- max bigint
END AS ordcol
FROM a
ORDER BY ordcol, cola
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:B7D8A70A-C130-4C6C-9CDF-B453380E810D@.microsoft.com...
> Hi,
> I have a table with a Varchar column(col1) contains values as below,
> Low
> Upper
> 4
> Lp
> 31
> 1
> 2
> Jack
> 3
> default order/order by col1 is
> 1
> 2
> 3
> 31
> 4
> Jack
> Low
> Lp
> Upper
>
> I want to order this column in such a way that
> blank spaces first, numbers second(in ascending) and alphabet...
> 1
> 2
> 3
> 4
> 31
> Jack
> Low
> Lp
> Upper
> Please advise.
> Thanks,
> Soura
|||On Mon, 5 Dec 2005 07:05:03 -0800, SouRa wrote:

>Hi,
>I have a table with a Varchar column(col1) contains values as below,
>Low
>Upper
>4
>Lp
>31
>1
>2
>Jack
>3
>default order/order by col1 is
>1
>2
>3
>31
>4
>Jack
>Low
>Lp
>Upper
>
>I want to order this column in such a way that
>blank spaces first, numbers second(in ascending) and alphabet...
>1
>2
>3
>4
>31
>Jack
>Low
>Lp
>Upper
>Please advise.
>Thanks,
>Soura
Hi Soura,
ORDER BY CASE WHEN col1 = ' ' THEN 1
WHEN col1 NOT LIKE '%[^0-9]%' THEN 2
ELSE 3
END,
CASE WHEN col1 NOT LIKE '%[^0-9]%' THEN CAST(col1 AS int) END,
col1
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

ORDER - varchar column contains Char & int values

Hi,
I have a table with a Varchar column(col1) contains values as below,
Low
Upper
4
Lp
31
1
2
Jack
3
default order/order by col1 is
1
2
3
31
4
Jack
Low
Lp
Upper
I want to order this column in such a way that
blank spaces first, numbers second(in ascending) and alphabet...
1
2
3
4
31
Jack
Low
Lp
Upper
Please advise.
Thanks,
SouraUSE tempdb
GO
CREATE TABLE a (cola varchar(10))
GO
INSERT INTO a VALUES('Low')
INSERT INTO a VALUES('Upper')
INSERT INTO a VALUES('4')
INSERT INTO a VALUES('Lp')
INSERT INTO a VALUES('31')
INSERT INTO a VALUES('1')
INSERT INTO a VALUES('2')
INSERT INTO a VALUES('Jack')
INSERT INTO a VALUES('3')
GO
SELECT cola,
CASE
WHEN ISNUMERIC(cola) = 1 THEN CAST(cola AS bigint)
ELSE 9223372036854775807 -- max bigint
END AS ordcol
FROM a
ORDER BY ordcol, cola
--
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:B7D8A70A-C130-4C6C-9CDF-B453380E810D@.microsoft.com...
> Hi,
> I have a table with a Varchar column(col1) contains values as below,
> Low
> Upper
> 4
> Lp
> 31
> 1
> 2
> Jack
> 3
> default order/order by col1 is
> 1
> 2
> 3
> 31
> 4
> Jack
> Low
> Lp
> Upper
>
> I want to order this column in such a way that
> blank spaces first, numbers second(in ascending) and alphabet...
> 1
> 2
> 3
> 4
> 31
> Jack
> Low
> Lp
> Upper
> Please advise.
> Thanks,
> Soura|||On Mon, 5 Dec 2005 07:05:03 -0800, SouRa wrote:
>Hi,
>I have a table with a Varchar column(col1) contains values as below,
>Low
>Upper
>4
>Lp
>31
>1
>2
>Jack
>3
>default order/order by col1 is
>1
>2
>3
>31
>4
>Jack
>Low
>Lp
>Upper
>
>I want to order this column in such a way that
>blank spaces first, numbers second(in ascending) and alphabet...
>1
>2
>3
>4
>31
>Jack
>Low
>Lp
>Upper
>Please advise.
>Thanks,
>Soura
Hi Soura,
ORDER BY CASE WHEN col1 = ' ' THEN 1
WHEN col1 NOT LIKE '%[^0-9]%' THEN 2
ELSE 3
END,
CASE WHEN col1 NOT LIKE '%[^0-9]%' THEN CAST(col1 AS int) END,
col1
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

ORDER - varchar column contains Char & int values

Hi,
I have a table with a Varchar column(col1) contains values as below,
Low
Upper
4
Lp
31
1
2
Jack
3
default order/order by col1 is
1
2
3
31
4
Jack
Low
Lp
Upper
I want to order this column in such a way that
blank spaces first, numbers second(in ascending) and alphabet...
1
2
3
4
31
Jack
Low
Lp
Upper
Please advise.
Thanks,
SouraUSE tempdb
GO
CREATE TABLE a (cola varchar(10))
GO
INSERT INTO a VALUES('Low')
INSERT INTO a VALUES('Upper')
INSERT INTO a VALUES('4')
INSERT INTO a VALUES('Lp')
INSERT INTO a VALUES('31')
INSERT INTO a VALUES('1')
INSERT INTO a VALUES('2')
INSERT INTO a VALUES('Jack')
INSERT INTO a VALUES('3')
GO
SELECT cola,
CASE
WHEN ISNUMERIC(cola) = 1 THEN CAST(cola AS bigint)
ELSE 9223372036854775807 -- max bigint
END AS ordcol
FROM a
ORDER BY ordcol, cola
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:B7D8A70A-C130-4C6C-9CDF-B453380E810D@.microsoft.com...
> Hi,
> I have a table with a Varchar column(col1) contains values as below,
> Low
> Upper
> 4
> Lp
> 31
> 1
> 2
> Jack
> 3
> default order/order by col1 is
> 1
> 2
> 3
> 31
> 4
> Jack
> Low
> Lp
> Upper
>
> I want to order this column in such a way that
> blank spaces first, numbers second(in ascending) and alphabet...
> 1
> 2
> 3
> 4
> 31
> Jack
> Low
> Lp
> Upper
> Please advise.
> Thanks,
> Soura|||On Mon, 5 Dec 2005 07:05:03 -0800, SouRa wrote:

>Hi,
>I have a table with a Varchar column(col1) contains values as below,
>Low
>Upper
>4
>Lp
>31
>1
>2
>Jack
>3
>default order/order by col1 is
>1
>2
>3
>31
>4
>Jack
>Low
>Lp
>Upper
>
>I want to order this column in such a way that
>blank spaces first, numbers second(in ascending) and alphabet...
>1
>2
>3
>4
>31
>Jack
>Low
>Lp
>Upper
>Please advise.
>Thanks,
>Soura
Hi Soura,
ORDER BY CASE WHEN col1 = ' ' THEN 1
WHEN col1 NOT LIKE '%[^0-9]%' THEN 2
ELSE 3
END,
CASE WHEN col1 NOT LIKE '%[^0-9]%' THEN CAST(col1 AS int) END,
col1
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Order By

i want to use an order by clause. my issue is that the values i have are alpha and i need the top of the order to be 'A', 'S', then what ever. how can i sort this column and have the top two being 'A's and 'S's??
thanks in advance
eThere are several ways. Perhaps the laziest is to select a field only for ordering purposes.

SELECT lastname, toto, tata
CASE WHEN lastname LIKE ('A%') THEN 1
WHEN lastname LIKE('S%') THEN 2
ELSE 3
END as Ord
ORDER by ord, lastname

Another is to use several queries and the UNION operator

SELECT .. FROM ..
WHERE
lastname like ('A%')
ORDER BY lastname
UNION
...
where lastname like ('S%')
ORDER BY lastname

UNION
WHER lastname not like ('A%') and lastname not like ('S%')
ORDER BY lastname|||i think i can make that work... thanks :)

Oracle OLEDB drivers problem with Numbers

Hi All:

I am using oracle oledb drivers to write to a oledb destination.

if i give decimal values to decimal fields in the source table, i get the same in destination. But if the input is integers, in some cases, the value in the destination is different from that of source

Source Target

50 50.00
100 0.000
111 111.000
600 0.0000
520 20.00
178 178
4546.50 4546.50

I have Sql server SP2 9.0.3042 installed on my machine. Please let me know if theres something i am missing out.

Thanks,

Vipul

There is no such thing as an integer in Oracle. It's a NUMERIC(p,0) field. That is, it has no scale. SSIS doesn't support this at the moment. Instead, write your query such that you convert the "integer" field into a NUMERIC(p+1,1) field, or something like that. Then map to a decimal field in SSIS. From there, if you want integers out of the data, use a derived column to cast the values to integers.|||

Let me put it this way phil. Have you come across decimal data being changed from source to target without any transforms in between ? I was not correct in putting the question but the cause of my concern is that if the source has 500 how the target is getting it as 0.

Is there some problem in SSIS for this or this is oracle oledb driver problem?

|||Have you looked at the data with a data viewer to see what is contained there? You might need to recreate the OLE DB source.|||

ya i have viewed data with the data viewer before the oledb destination. Data is fine till data viewer. Theres something happening in oledb destination and thats why i suspect the drivers.

Also, the same behaviour is not happening on one of my other machine. The machine confguration of both the machine are same. I am executing the same pacakge from both the machines.

The version of software on both mahcine are:

-

SQL Server sp2 9.0.3042

Oracle 10g

Let me know your thoughts on this..

|||Is your destination SQL Server?

Have you looked at the advanced properties of the OLE DB Destination to ensure that the data types for all of the columns are correct?|||

Destination is Oracle.

And i have checked all the datatypes as per ur suggestion but still the problem exists.

|||I'm going to have to bow out as I don't have an Oracle instance to test with.