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 clause not sorting correctly!

My problem is simple but I cannot see a way to get around it.
I am trying to do an ORDER BY on a varchar field, and it keeps putting
"_" (underscore) before "0" (zero).
i.e. I want it to work like
"_" in ascii is 95, "0" in ascii is 48, therefore "0" comes first
WHY IS IT DOING THIS?!!! I am pulling my hair out in frustration.

>From an increasingly balding
Kaz> I am trying to do an ORDER BY on a varchar field, and it keeps putting
> "_" (underscore) before "0" (zero).
What is the column collation? The collation determines sorting and
comparison rules. Consider:
CREATE TABLE dbo.foo
(
col1 varchar(10) COLLATE Latin1_General_CI_AS,
col2 varchar(10) COLLATE Latin1_General_BIN
)
INSERT INTO dbo.foo VALUES('0', '0')
INSERT INTO dbo.foo VALUES('_', '_')
SELECT * FROM dbo.foo
ORDER BY col1
SELECT * FROM dbo.foo
ORDER BY col2
GO
You can also specify an explicit collation in your ORDER BY, although
indexes can't be used to facilitate efficient ordering due to the COLLATE
expression:
SELECT * FROM dbo.foo
ORDER BY col1 COLLATE Latin1_General_BIN
Hope this helps.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185417750.022600.219110@.i13g2000prf.googlegroups.com...
> My problem is simple but I cannot see a way to get around it.
> I am trying to do an ORDER BY on a varchar field, and it keeps putting
> "_" (underscore) before "0" (zero).
> i.e. I want it to work like
> "_" in ascii is 95, "0" in ascii is 48, therefore "0" comes first
> WHY IS IT DOING THIS?!!! I am pulling my hair out in frustration.
>
> Kaz
>|||Thank you Dan for your response.
My place of work has two versions of SQL server running on different
machines - SQL Server 2000 and SQL Server 7.00.
This helps with the SQL Server 2000 machines, but won't work on SQL
Server 7.0.
Is there any way to get it sort correctly on SQL Server 7.0?
Karen|||> This helps with the SQL Server 2000 machines, but won't work on SQL
> Server 7.0.
> Is there any way to get it sort correctly on SQL Server 7.0?
Yes, Karen. Collations were introduced in SQL Server 2000. In SQL 7 and
earlier versions, you can CAST are CONVERT to varbinary to get binary
sorting you want:
SELECT * FROM dbo.foo
ORDER BY CAST(col1AS varbinary(10))
Hope this helps.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185511629.895148.96300@.e9g2000prf.googlegroups.com...
> Thank you Dan for your response.
> My place of work has two versions of SQL server running on different
> machines - SQL Server 2000 and SQL Server 7.00.
> This helps with the SQL Server 2000 machines, but won't work on SQL
> Server 7.0.
> Is there any way to get it sort correctly on SQL Server 7.0?
> Karen
>|||Thank you SO much Dan, you have saved my sanity (or what little of it
I have left )
Karen|||I'm glad I was able to help.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185766768.945039.300220@.x35g2000prf.googlegroups.com...
> Thank you SO much Dan, you have saved my sanity (or what little of it
> I have left )
> Karen
>

ORDER BY clause not sorting correctly!

My problem is simple but I cannot see a way to get around it.
I am trying to do an ORDER BY on a varchar field, and it keeps putting
"_" (underscore) before "0" (zero).
i.e. I want it to work like
"_" in ascii is 95, "0" in ascii is 48, therefore "0" comes first
WHY IS IT DOING THIS?!!! I am pulling my hair out in frustration.

>From an increasingly balding
Kaz
> I am trying to do an ORDER BY on a varchar field, and it keeps putting
> "_" (underscore) before "0" (zero).
What is the column collation? The collation determines sorting and
comparison rules. Consider:
CREATE TABLE dbo.foo
(
col1 varchar(10) COLLATE Latin1_General_CI_AS,
col2 varchar(10) COLLATE Latin1_General_BIN
)
INSERT INTO dbo.foo VALUES('0', '0')
INSERT INTO dbo.foo VALUES('_', '_')
SELECT * FROM dbo.foo
ORDER BY col1
SELECT * FROM dbo.foo
ORDER BY col2
GO
You can also specify an explicit collation in your ORDER BY, although
indexes can't be used to facilitate efficient ordering due to the COLLATE
expression:
SELECT * FROM dbo.foo
ORDER BY col1 COLLATE Latin1_General_BIN
Hope this helps.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185417750.022600.219110@.i13g2000prf.googlegr oups.com...
> My problem is simple but I cannot see a way to get around it.
> I am trying to do an ORDER BY on a varchar field, and it keeps putting
> "_" (underscore) before "0" (zero).
> i.e. I want it to work like
> "_" in ascii is 95, "0" in ascii is 48, therefore "0" comes first
> WHY IS IT DOING THIS?!!! I am pulling my hair out in frustration.
> Kaz
>
|||Thank you Dan for your response.
My place of work has two versions of SQL server running on different
machines - SQL Server 2000 and SQL Server 7.00.
This helps with the SQL Server 2000 machines, but won't work on SQL
Server 7.0.
Is there any way to get it sort correctly on SQL Server 7.0?
Karen
|||> This helps with the SQL Server 2000 machines, but won't work on SQL
> Server 7.0.
> Is there any way to get it sort correctly on SQL Server 7.0?
Yes, Karen. Collations were introduced in SQL Server 2000. In SQL 7 and
earlier versions, you can CAST are CONVERT to varbinary to get binary
sorting you want:
SELECT * FROM dbo.foo
ORDER BY CAST(col1AS varbinary(10))
Hope this helps.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185511629.895148.96300@.e9g2000prf.googlegrou ps.com...
> Thank you Dan for your response.
> My place of work has two versions of SQL server running on different
> machines - SQL Server 2000 and SQL Server 7.00.
> This helps with the SQL Server 2000 machines, but won't work on SQL
> Server 7.0.
> Is there any way to get it sort correctly on SQL Server 7.0?
> Karen
>
|||Thank you SO much Dan, you have saved my sanity (or what little of it
I have left )
Karen
|||I'm glad I was able to help.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185766768.945039.300220@.x35g2000prf.googlegr oups.com...
> Thank you SO much Dan, you have saved my sanity (or what little of it
> I have left )
> Karen
>

ORDER BY clause not sorting correctly!

My problem is simple but I cannot see a way to get around it.
I am trying to do an ORDER BY on a varchar field, and it keeps putting
"_" (underscore) before "0" (zero).
i.e. I want it to work like
"_" in ascii is 95, "0" in ascii is 48, therefore "0" comes first
WHY IS IT DOING THIS?!!! I am pulling my hair out in frustration.
>From an increasingly balding
Kaz> I am trying to do an ORDER BY on a varchar field, and it keeps putting
> "_" (underscore) before "0" (zero).
What is the column collation? The collation determines sorting and
comparison rules. Consider:
CREATE TABLE dbo.foo
(
col1 varchar(10) COLLATE Latin1_General_CI_AS,
col2 varchar(10) COLLATE Latin1_General_BIN
)
INSERT INTO dbo.foo VALUES('0', '0')
INSERT INTO dbo.foo VALUES('_', '_')
SELECT * FROM dbo.foo
ORDER BY col1
SELECT * FROM dbo.foo
ORDER BY col2
GO
You can also specify an explicit collation in your ORDER BY, although
indexes can't be used to facilitate efficient ordering due to the COLLATE
expression:
SELECT * FROM dbo.foo
ORDER BY col1 COLLATE Latin1_General_BIN
--
Hope this helps.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185417750.022600.219110@.i13g2000prf.googlegroups.com...
> My problem is simple but I cannot see a way to get around it.
> I am trying to do an ORDER BY on a varchar field, and it keeps putting
> "_" (underscore) before "0" (zero).
> i.e. I want it to work like
> "_" in ascii is 95, "0" in ascii is 48, therefore "0" comes first
> WHY IS IT DOING THIS?!!! I am pulling my hair out in frustration.
>>From an increasingly balding
> Kaz
>|||Thank you Dan for your response.
My place of work has two versions of SQL server running on different
machines - SQL Server 2000 and SQL Server 7.00.
This helps with the SQL Server 2000 machines, but won't work on SQL
Server 7.0.
Is there any way to get it sort correctly on SQL Server 7.0?
Karen|||> This helps with the SQL Server 2000 machines, but won't work on SQL
> Server 7.0.
> Is there any way to get it sort correctly on SQL Server 7.0?
Yes, Karen. Collations were introduced in SQL Server 2000. In SQL 7 and
earlier versions, you can CAST are CONVERT to varbinary to get binary
sorting you want:
SELECT * FROM dbo.foo
ORDER BY CAST(col1AS varbinary(10))
--
Hope this helps.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185511629.895148.96300@.e9g2000prf.googlegroups.com...
> Thank you Dan for your response.
> My place of work has two versions of SQL server running on different
> machines - SQL Server 2000 and SQL Server 7.00.
> This helps with the SQL Server 2000 machines, but won't work on SQL
> Server 7.0.
> Is there any way to get it sort correctly on SQL Server 7.0?
> Karen
>|||Thank you SO much Dan, you have saved my sanity (or what little of it
I have left :) )
Karen|||I'm glad I was able to help.
--
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185766768.945039.300220@.x35g2000prf.googlegroups.com...
> Thank you SO much Dan, you have saved my sanity (or what little of it
> I have left :) )
> Karen
>

Order by clause

hello everybody. i have a table named employee. where there is just a single column named id which is of type varchar. this is because i just wanted to play with order by clause. :D

in the table, i have CCA, cbC, cBC, CbC, CBC. when i queried with select * from employee order by id; the result is as followed-
CBC
cBC
cbC
CbC
CCA

how order by clause worked here, can anybody explain? thanks in advance...for the purpose of the ORDER BY, the collation you are using means that CBC, cBC, cbC, and CbC are all equivalent

CCA, of course, would come after all of them|||some databases treat upper & lower case the same, some dont, some can do either
some front ends likewise

so read the documentation with your db and your front end and see if you can select which order you want.

for example if you are using microsoft access as your front end 'option compare' may be worth investigating

MySQL has a mechanism at the database level to handle this sort of issue, and Im sure that will be the same for most other serious servers|||But if i write select * from employee order by id desc then the result is:
CCA
CBC
cBC
cbC
CbC

i don't have any idea how order by is working here...|||with DESC, CCA comes first, then all the rows with CBC equivalents|||if all rows with CBC are equivalent then why CbC comes last?? didn't get your point. plz explain.|||Yeah, that is a good point...
if SELECT * FROM employee ORDER BY id; gives the result
CBC
cBC
cbC
CbC
CCA

then SELECT * FROM employee ORDER BY id DESC; should provide-
CCA
CbC
cbC
cBC
CBC

why instead of it, the result is like this-
CCA
CBC
cBC
cbC
CbC?

or is it like the order of 4 CBCs doesn't really matter?:shocked:|||you know, it's beginning to look as if the order of the CBCs doesn't really matter because they're all equivalent

:)|||The sort order in databases is related with the Collation of that database.
For example Latin1_General_CI_AS is a Case Insensitive Accent Sensitive collation.

For a list of collations in MS SQL Server, you can use fn_helpcollations() function.
You can read the article http://www.kodyaz.com/content/fnhelpcollations.aspx on fn_helpcollations()

Eralper
http://www.kodyaz.com|||If the collation is case-insensitive, then to your database CBC and cbc appear to be the same thing... It doesn't matter which order they appear because for the purposes of comparision they are equal.

-PatP

ORDER BY [Date] isnt working since its not a DateTime (its a varchar!)

Hi everyone.

I know, I know, it should have been a datetime from the start...but here's the problem.

I'm trying to sort by my date field but because it looks like: "04/03/2004 12:14:21 PM" it's not ordering it properly using:

ORDER BY [Date]

Are there any work arounds for this? Is there some way of doing:

ORDER BY covert(datetime, [Date], 103) or something?

Cheers
AndrewSELECT Convert(datetime, [Date], 103), Columnnames FROM TABLENAME
ORDER BY 1|||Thanks heaps, I'll give it a go!

Andrew|||Worked perfectly, though I chosed to:

SELECT convert(datetime, [Date], 103) AS 'Converted'
WHERE ...
ORDER BY Converted

just for looks :)

Thanks again!

ORDER BY <VarChar Field>

Hi group,

I've got a table with two columns named [Year] and [Month]. They are both defined as VarChar.

Question:
Is it possible to ORDER THEM as if they where of type DateTime?

EG
select [year], [month]
from tbl_WeightedAverageGenerated
where [Year] = 2006
ORDER BY [Month]

Returns:
2006, 10
2006, 11
2006, 12
2006, 5
2006, 6
etc...

I need it to return:
2006 5
2006 6
2006 7
2006 8
2006 9
2006 10
2006 11
2006 12

Is this possible....and how?

TIA

Regards,

SDerix

Yes, cast them as integers first.

select [year], [month]
from tbl_WeightedAverageGenerated
where [Year] = 2006
ORDER BY cast([Month] as int)

-Jamie

Order by & case

I am trying to use a case statment to indicate sort order. Everything worked fine untill I added "desc".

create table #tmp(f1 varchar(25), dtObs datetime, dtCnt datetime)
insert into #tmp values('ABC','01-Jan-2003','02-Jan-2003')
insert into #tmp values('BCD','01-Jan-2003','03-Jan-2003')
insert into #tmp values('CDE','01-Jan-2003','04-Jan-2003')
insert into #tmp values('DEF','02-Jan-2003','03-Jan-2003')
insert into #tmp values('EFG','02-Jan-2003','04-Jan-2003')
insert into #tmp values('FGH','02-Jan-2003','05-Jan-2003')

declare @.Order tinyint
set @.Order = 1
select *
from #tmp
order by case @.Order when 1 then 'dtObs'
when 2 then 'dtCnt'
when 3 then 'dtObs desc'
when 4 then 'dtCnt desc'
else null end

any suggestion on how to do this?select *
from #tmp
order by case @.Order when 1 then 'dtObs'
when 2 then 'dtCnt'
else null end
,case @.Order when 3 then 'dtObs'
when 4 then 'dtCnt'
else null end DESC|||Thanks!|||Pleasure to help you.|||I am using a Case statement within my Order By clause, but I have columns that I want to use that were created in my Select clause as expressions. I cannot seem to use those columns in my Case statement. Is there a way to do this?? Thank you very much for your help!!!sql

Order by "string not empty"?

Hi ng.

I have a varchar field in my table, called Name.

I wanna do a selection, which is ordered by whether this field is empty or
not.

E.g. something like:

SELECT
UserID
ORDER BY
Name <> '';

- - -

How can I accomplish this?

TIA.
Klaus.This will put the non-empty rows first:

SELECT userid
FROM SomeTable
ORDER BY name DESC

--
David Portas
SQL Server MVP
--|||> This will put the non-empty rows first:
> SELECT userid
> FROM SomeTable
> ORDER BY name DESC

It just doesnt seem to be a wise way to do it - why do string sorting when
the only thing needed is whether its empty or not...?|||On Tue, 8 Jun 2004 15:21:36 +0200, Klaus Petersen wrote:

>> This will put the non-empty rows first:
>>
>> SELECT userid
>> FROM SomeTable
>> ORDER BY name DESC
>It just doesnt seem to be a wise way to do it - why do string sorting when
>the only thing needed is whether its empty or not...?

Hi Klaus,

Maybe because ORDER BY CASE WHEN name IS NULL THEN 1 ELSE 2 END is a lot
harder to read and maintain?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||In addition to Hugo's point, using a CASE expression would require that the
expression be evaluated for every single row. Compare performance of the two
approaches and see which works best for you.

--
David Portas
SQL Server MVP
--

order by

Hi everyone,
I have a select statement of the form
SELECT * FROM temp ORDER BY time

I have a scalar function ConvertToMinutes that takes in varchar and returns int, is there any way to do something like this SELECT * FROM temp ORDER BY ConvertToMinutes(time). I tried doing this and it doesn't work (it tells me ConvertToMinutes is not a built-in function). Please guide me as to how I would accomplish this. Thanks in advance.

P.S. Clarification: I am trying to order the table temp by the value returned by the function ConvertToMinutes on the coloumn time.I am not exactly sure if you can use function in the order by clause. try

SELECT * FROM temp ORDER BY dbo.ConvertToMinutes(convert(varchar,time))

|||

I do believe ndinakar's method will work, however, if it does not, you can also do this:

SELECT *
FROM (SELECT *,dbo.converttominutes(time) mytime FROM temp) t1
ORDER BY mytime

or this:

SELECT temp.*
FROM temp
JOIN (SELECT DISTINCT time,dbo.converttominutes(time) mytime FROM temp) t1 ON (temp.time=t1.time)
ORDER BY mytime

|||Hey guys thanks for the answers. I tried the first solution and it worked, so I did not try the second one. But thanks to both of you for answering.sql

order by

select constant ,description from action order by description
--Here description is a varchar datatype. And collation for database is
SQL_LAtin1_General_CP1_CI_AS.
My question is what order the sorting happens.It's not ASCII value because
numbers come before _ in ASCII.
Thanks
constant description
-- --
6 *uuu
7 :ooo
2 _addd
3 0111
4 3000
8 AAA
1 add
5 T000Abraham,
Perhaps this helps:
If you do not specify a binary collation, SQL Server uses the dictionary
ordering of the collation you have chosen. Dictionary order means characters
are not sorted or compared based only on their bit patterns. The collation
follows the conventions of the associated language regarding the proper
sequence for characters. For example, case-insensitive sort orders must use
dictionary rules to determine which lowercase and uppercase bit patterns are
equal.
Russell Fields
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:eRLcMFO7DHA.4060@.tk2msftngp13.phx.gbl...
> select constant ,description from action order by description
> --Here description is a varchar datatype. And collation for database is
> SQL_LAtin1_General_CP1_CI_AS.
> My question is what order the sorting happens.It's not ASCII value because
> numbers come before _ in ASCII.
> Thanks
> constant description
> -- --
> 6 *uuu
> 7 :ooo
> 2 _addd
> 3 0111
> 4 3000
> 8 AAA
> 1 add
> 5 T000
>sql

order by

There is a field called field1 in a table called table1
Field1 is varchar(50)
Data inside field1 is such as:

3T
apr 2007
feb 2001
jan 2001
dec 1999
...

I am writing a sql query to return the data so that the field1 is sorted:
ie:

3T
dec 1999
jan 2001
feb 2001
apr 2007
...

This is what I have in the order by clause:

cast('01-' + replace(field1, ' ', '-') as datetime)

The problem is if there is something else such as '3T' or another text inside this field1.
Should there be a case statement inside the oprder by clause?

Please note that if there is any text which does not seem to be a date then it should appear first in the list as shown in the example above.
Thanks

Code Snippet

select'3T'as data

into #t

union allselect'feb 2001'

union allselect'apr 2007'

union allselect'dec 1999'

union allselect'jan 2001'

selectcaseisdate(data)when 1 thenconvert(varchar(10),cast(data asdatetime), 102)else data end

from #t

orderbyisdate(data),

caseisdate(data)when 1 thenconvert(varchar(10),cast(data asdatetime), 102)else data end

|||

Use the following query..

Code Snippet

Create Table #data (

[Col] Varchar(100)

);

Insert Into #data Values('apr 2007');

Insert Into #data Values('feb 2001');

Insert Into #data Values('jan 2001');

Insert Into #data Values('dec 1999');

Insert Into #data Values('Americas');

Insert Into #data Values('APJ');

Insert Into #data Values('EMEA');

Insert Into #data Values('1982');

Insert Into #data Values('AJP09837');

Select * From #data Order By

Case When Isdate(Col)=1 Then 1 End,

Case When IsDate(Col)=1 Then cast(Col as datetime) End,

Col

drop table #data

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 by

select constant ,description from action order by description
--Here description is a varchar datatype. And collation for database is
SQL_LAtin1_General_CP1_CI_AS.
My question is what order the sorting happens.It's not ASCII value because
numbers come before _ in ASCII.
Thanks
constant description
-- --
6 *uuu
7 :ooo
2 _addd
3 0111
4 3000
8 AAA
1 add
5 T000Abraham,
Perhaps this helps:
If you do not specify a binary collation, SQL Server uses the dictionary
ordering of the collation you have chosen. Dictionary order means characters
are not sorted or compared based only on their bit patterns. The collation
follows the conventions of the associated language regarding the proper
sequence for characters. For example, case-insensitive sort orders must use
dictionary rules to determine which lowercase and uppercase bit patterns are
equal.
Russell Fields
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:eRLcMFO7DHA.4060@.tk2msftngp13.phx.gbl...
> select constant ,description from action order by description
> --Here description is a varchar datatype. And collation for database is
> SQL_LAtin1_General_CP1_CI_AS.
> My question is what order the sorting happens.It's not ASCII value because
> numbers come before _ in ASCII.
> Thanks
> constant description
> -- --
> 6 *uuu
> 7 :ooo
> 2 _addd
> 3 0111
> 4 3000
> 8 AAA
> 1 add
> 5 T000
>

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)

Oracle and unicode

Since the .net oracle providers are the only ones that work in x64 it is extremely annoying that it insists on mapping ALL varchar columns in oracle to wstring. Is there a way to fix this? The oledb providers don't have this problem but I don't like being forced to use the 32 bit runtime as a workaround.I remember that this has already been discussed:
I was told, that all .NET providers return UNICODE text. You have to do an explicit data conversion with ALL your fields.

@.Donald Farmer: Since you've said, that when developing SSIS it was meant to avoid implicit data conversion due to performance issues you've had with DTS, I'm wondering how this paradigm copes with this?

@.Brent: Sorry I cannot help...|||

Sorry, I didn't see that post.

I'm just having a hard time explaining to my developers why there's 18 different workarounds to get oracle to work in the first place. The last thing they want to do is explicitly map 150 different columns.

|||

Brent Mills wrote:

Since the .net oracle providers are the only ones that work in x64 it is extremely annoying that it insists on mapping ALL varchar columns in oracle to wstring. Is there a way to fix this? The oledb providers don't have this problem but I don't like being forced to use the 32 bit runtime as a workaround.

i'm not sure if this will help, but openlink has a 64 bit ole db to obdc provider: http://uda.openlinksw.com/oledb/st/oledb-odbc-st/