order by turkish character problem

i want to order by name coloumn.but if i do that ordering is (A,B,C,D,E,F......X,Y,Z,?,?,?,ü). i want to order these letters at place of turkish alphebetical order.how can i do that? i'm sorry for my english.Moving to the Transact-SQL forum from SSIS.|||

1. Check your column datatype it should be NVarchar

2. While inserting the value the string constants should be enclosed with N'....'

3. If the above 2 points are not solving your problem, then create the column with rite collate..

create table #test

(

alphas nvarchar(1) Collate Turkish_CI_AI

)

insert into #test values(N'A');

insert into #test values(N'D');

insert into #test values(N'Y');

insert into #test values(N'?');

insert into #test values(N'?');

insert into #test values(N'?');

insert into #test values(N'ü');

Select alphas from #test Order By alphas Desc

ORDER BY DATETIME field BUG?

I was under the impression that the DATETIME field was stored
internally in SQL server as
two integers?
Any idea how to get correct ordering of a datetime field?
I have two records containing a datetime field with these values and
this is the order that
order by displays them in when I say "select * from blah ORDER BY
datetimefield"
Dec 30 1989 11:55PM
Dec 31 1989 1:05PM
Dec 31 1989 7:15AM
Dec 31 1989 7:35PM
Dec 31 1989 9:10AM
Dec 31 1989 10:20AM
Dec 31 1989 11:10AM
Dec 31 1989 11:40AM
Jan 1 1990 1:20PM
Jan 1 1990 12:35PM
Why doesn't Order by on a datetime field sort correctly instead of
treating them like strings?Can you show us the result of sp_help tablename and/or the CREATE TABLE /
INSERT / SELECT to reproduce this problem? It looks like they are stored
and/or sorted as varchar, not datetime, or you are sorting the result of a
convert, not the column itself. (The most telltale thing is thathe result
does not contain seconds or milliseconds).
"wolfen42 at gmail.com" <wolfen42@.gmail.com> wrote in message
news:1150996807.502951.137230@.m73g2000cwd.googlegroups.com...
>I was under the impression that the DATETIME field was stored
> internally in SQL server as
> two integers?
> Any idea how to get correct ordering of a datetime field?
> I have two records containing a datetime field with these values and
> this is the order that
> order by displays them in when I say "select * from blah ORDER BY
> datetimefield"
> Dec 30 1989 11:55PM
> Dec 31 1989 1:05PM
> Dec 31 1989 7:15AM
> Dec 31 1989 7:35PM
> Dec 31 1989 9:10AM
> Dec 31 1989 10:20AM
> Dec 31 1989 11:10AM
> Dec 31 1989 11:40AM
> Jan 1 1990 1:20PM
> Jan 1 1990 12:35PM
> Why doesn't Order by on a datetime field sort correctly instead of
> treating them like strings?
>|||wolfen42 at gmail.com,

> Any idea how to get correct ordering of a datetime field?
Are you sure that column is a datetime one?. Seems like it is charecter data
type instead atetime. See next statements.
select
*
from
(
select cast('Dec 30 1989 11:55PM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 1:05PM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 7:15AM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 7:35PM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 9:10AM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 10:20AM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 11:10AM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 11:40AM' as varchar(25)) as c1 union all
select cast('Jan 1 1990 1:20PM' as varchar(25)) as c1 union all
select cast('Jan 1 1990 12:35PM' as varchar(25)) as c1
) as t1
order by
c1
select
*
from
(
select cast('Dec 30 1989 11:55PM' as datetime) as c1 union all
select cast('Dec 31 1989 1:05PM' as datetime) as c1 union all
select cast('Dec 31 1989 7:15AM' as datetime) as c1 union all
select cast('Dec 31 1989 7:35PM' as datetime) as c1 union all
select cast('Dec 31 1989 9:10AM' as datetime) as c1 union all
select cast('Dec 31 1989 10:20AM' as datetime) as c1 union all
select cast('Dec 31 1989 11:10AM' as datetime) as c1 union all
select cast('Dec 31 1989 11:40AM' as datetime) as c1 union all
select cast('Jan 1 1990 1:20PM' as datetime) as c1 union all
select cast('Jan 1 1990 12:35PM' as datetime) as c1
) as t1
order by
c1
go
AMB
"wolfen42 at gmail.com" wrote:

> I was under the impression that the DATETIME field was stored
> internally in SQL server as
> two integers?
> Any idea how to get correct ordering of a datetime field?
> I have two records containing a datetime field with these values and
> this is the order that
> order by displays them in when I say "select * from blah ORDER BY
> datetimefield"
> Dec 30 1989 11:55PM
> Dec 31 1989 1:05PM
> Dec 31 1989 7:15AM
> Dec 31 1989 7:35PM
> Dec 31 1989 9:10AM
> Dec 31 1989 10:20AM
> Dec 31 1989 11:10AM
> Dec 31 1989 11:40AM
> Jan 1 1990 1:20PM
> Jan 1 1990 12:35PM
> Why doesn't Order by on a datetime field sort correctly instead of
> treating them like strings?
>|||wolfen42 at gmail.com wrote:
> I was under the impression that the DATETIME field was stored
> internally in SQL server as
> two integers?
> Any idea how to get correct ordering of a datetime field?
> I have two records containing a datetime field with these values and
> this is the order that
> order by displays them in when I say "select * from blah ORDER BY
> datetimefield"
> Dec 30 1989 11:55PM
> Dec 31 1989 1:05PM
> Dec 31 1989 7:15AM
> Dec 31 1989 7:35PM
> Dec 31 1989 9:10AM
> Dec 31 1989 10:20AM
> Dec 31 1989 11:10AM
> Dec 31 1989 11:40AM
> Jan 1 1990 1:20PM
> Jan 1 1990 12:35PM
> Why doesn't Order by on a datetime field sort correctly instead of
> treating them like strings?
>
Are you sure that field is of type DATETIME? I just inserted these same
values into a table with a DATETIME field, and they sort properly. If I
put them into a VARCHAR field, then they do not.|||Can you provide a sample that works this way? It sorts finr for me.
CREATE TABLE JustADate
(Dt datetime)
INSERT JustADate VALUES('Dec 30 1989 11:55PM')
INSERT JustADate VALUES('Dec 31 1989 1:05PM')
INSERT JustADate VALUES('Dec 31 1989 7:15AM')
INSERT JustADate VALUES('Dec 31 1989 7:35PM')
INSERT JustADate VALUES('Dec 31 1989 9:10AM')
INSERT JustADate VALUES('Dec 31 1989 10:20AM')
INSERT JustADate VALUES('Dec 31 1989 11:10AM')
INSERT JustADate VALUES('Dec 31 1989 11:40AM')
INSERT JustADate VALUES('Jan 1 1990 1:20PM')
INSERT JustADate VALUES('Jan 1 1990 12:35PM')
SELECT *
FROM JustADate
ORDER BY Dt
Dt
--
1989-12-30 23:55:00.000
1989-12-31 07:15:00.000
1989-12-31 09:10:00.000
1989-12-31 10:20:00.000
1989-12-31 11:10:00.000
1989-12-31 11:40:00.000
1989-12-31 13:05:00.000
1989-12-31 19:35:00.000
1990-01-01 12:35:00.000
1990-01-01 13:20:00.000
Roy Harvey
Beacon Falls, CT
On 22 Jun 2006 10:20:07 -0700, "wolfen42 at gmail.com"
<wolfen42@.gmail.com> wrote:

>I was under the impression that the DATETIME field was stored
>internally in SQL server as
>two integers?
>Any idea how to get correct ordering of a datetime field?
>I have two records containing a datetime field with these values and
>this is the order that
>order by displays them in when I say "select * from blah ORDER BY
>datetimefield"
>Dec 30 1989 11:55PM
>Dec 31 1989 1:05PM
>Dec 31 1989 7:15AM
>Dec 31 1989 7:35PM
>Dec 31 1989 9:10AM
>Dec 31 1989 10:20AM
>Dec 31 1989 11:10AM
>Dec 31 1989 11:40AM
>Jan 1 1990 1:20PM
>Jan 1 1990 12:35PM
>Why doesn't Order by on a datetime field sort correctly instead of
>treating them like strings?|||Unless you do this of course
CREATE TABLE JustADate
(Dt datetime)
INSERT JustADate VALUES('Dec 30 1989 11:55PM')
INSERT JustADate VALUES('Dec 31 1989 1:05PM')
INSERT JustADate VALUES('Dec 31 1989 7:15AM')
INSERT JustADate VALUES('Dec 31 1989 7:35PM')
INSERT JustADate VALUES('Dec 31 1989 9:10AM')
INSERT JustADate VALUES('Dec 31 1989 10:20AM')
INSERT JustADate VALUES('Dec 31 1989 11:10AM')
INSERT JustADate VALUES('Dec 31 1989 11:40AM')
INSERT JustADate VALUES('Jan 1 1990 1:20PM')
INSERT JustADate VALUES('Jan 1 1990 12:35PM')
SELECT convert(varchar(20),dt,100) as Dt
FROM JustADate
ORDER BY Dt
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Roy Harvey wrote:
> Can you provide a sample that works this way? It sorts finr for me.
> CREATE TABLE JustADate
> (Dt datetime)
> INSERT JustADate VALUES('Dec 30 1989 11:55PM')
> INSERT JustADate VALUES('Dec 31 1989 1:05PM')
> INSERT JustADate VALUES('Dec 31 1989 7:15AM')
> INSERT JustADate VALUES('Dec 31 1989 7:35PM')
> INSERT JustADate VALUES('Dec 31 1989 9:10AM')
> INSERT JustADate VALUES('Dec 31 1989 10:20AM')
> INSERT JustADate VALUES('Dec 31 1989 11:10AM')
> INSERT JustADate VALUES('Dec 31 1989 11:40AM')
> INSERT JustADate VALUES('Jan 1 1990 1:20PM')
> INSERT JustADate VALUES('Jan 1 1990 12:35PM')
> SELECT *
> FROM JustADate
> ORDER BY Dt
> Dt
> --
> 1989-12-30 23:55:00.000
> 1989-12-31 07:15:00.000
> 1989-12-31 09:10:00.000
> 1989-12-31 10:20:00.000
> 1989-12-31 11:10:00.000
> 1989-12-31 11:40:00.000
> 1989-12-31 13:05:00.000
> 1989-12-31 19:35:00.000
> 1990-01-01 12:35:00.000
> 1990-01-01 13:20:00.000
> Roy Harvey
> Beacon Falls, CT
> On 22 Jun 2006 10:20:07 -0700, "wolfen42 at gmail.com"
> <wolfen42@.gmail.com> wrote:
>|||> SELECT convert(varchar(20),dt,100) as Dt
> FROM JustADate
> ORDER BY Dt
I think it would be more clear what you are getting at if you use a
different alias, e.g.
SELECT convert(varchar(20),dt,100) as DtConverted
FROM JustADate
ORDER BY DtConverted
or
SELECT convert(varchar(20),dt,100)
FROM JustADate
ORDER BY convert(varchar(20),dt,100)|||You know, all of the replies so far have been insisting that this isnt
the way a DATETIME field behaves and that it must be a varchar field
somehow.
I "KNEW" that it couldn't possibly be a varchar field because even
though the table was created by my predecessor, why would this be the
only date/time field in the table that was a varchar instead of a
datetime field?
Ahem... well... I've been reminded once again. If it's an issue that
assumes some sort of consistent behavior on the part of this particular
person... assume the worst.
Sigh.
Thanks for forcing me to look at the table structures one more time and
notice that yes... this one date/time data field was an nvarchar for no
good reason.
Tracy McKibben wrote:
> wolfen42 at gmail.com wrote:
> Are you sure that field is of type DATETIME? I just inserted these same
> values into a table with a DATETIME field, and they sort properly. If I
> put them into a VARCHAR field, then they do not.|||wolfen42 at gmail.com wrote:
> You know, all of the replies so far have been insisting that this isnt
> the way a DATETIME field behaves and that it must be a varchar field
> somehow.
> I "KNEW" that it couldn't possibly be a varchar field because even
> though the table was created by my predecessor, why would this be the
> only date/time field in the table that was a varchar instead of a
> datetime field?
> Ahem... well... I've been reminded once again. If it's an issue that
> assumes some sort of consistent behavior on the part of this particular
> person... assume the worst.
> Sigh.
> Thanks for forcing me to look at the table structures one more time and
> notice that yes... this one date/time data field was an nvarchar for no
> good reason.
>
Sometimes it pays to ask the obvious, easy questions... :-)

order by case

I'm hoping there's an easy solution to this. When ordering with a case
statement this works:
select * from blah
order by case when @.sort = 1 then customer_no
else lastname end
But if I want the case statement to sort on multiple columns:
select * from blah
order by case when @.sort = 1 then customer_no
else lastname, firstname, customer_no end -- problem
It doesn't like the lastname, firstname, customer_no part.
Thanks for any suggestions.SG,
Try (as example(:
DECLARE @.SORT TINYINT
SET @.SORT = 0
SELECT *
FROM AUTHORS
ORDER BY CASE WHEN @.SORT = 1 THEN AU_LNAME ELSE STATE END, CITY
HTH
Jerry
"sg" <sg@.noemail.com> wrote in message
news:uwdKHY4yFHA.3892@.TK2MSFTNGP12.phx.gbl...
> I'm hoping there's an easy solution to this. When ordering with a case
> statement this works:
> select * from blah
> order by case when @.sort = 1 then customer_no
> else lastname end
> But if I want the case statement to sort on multiple columns:
> select * from blah
> order by case when @.sort = 1 then customer_no
> else lastname, firstname, customer_no end -- problem
> It doesn't like the lastname, firstname, customer_no part.
> Thanks for any suggestions.
>|||CASE is an expression and can only return one value. Using data type
precedence, all possible outcomes must either be or be convertible to a
common data type. You cannot use CASE for logic flow, e.g. to replace IF /
ELSE.
So, you need to break this out a little differently.
ORDER BY CASE @.sort
WHEN 1 THEN customer_no
ELSE lastname END,
CASE @.sort WHEN 1 THEN NULL ELSE firstname END
Though unless customer_no is not a key, this will probably work equally
well:
ORDER BY CASE @.sort
WHEN 1 THEN customer_no END,
lastname, firstname
If customer_no is sorted on first, then the rest of the order by clause
won't have any effect, because every customer_no is unique. If the first
ORDER BY is NULL, then the lastname, firstname sort order is obeyed.
In either case, adding customer_no to the end of the order by is only going
to come into play in the rare case that users have identical first/last
names, in which case, does it really matter which customer_no is displayed
first?
For some more information on this topic, see http://www.aspfaq.com/2501
"sg" <sg@.noemail.com> wrote in message
news:uwdKHY4yFHA.3892@.TK2MSFTNGP12.phx.gbl...
> I'm hoping there's an easy solution to this. When ordering with a case
> statement this works:
> select * from blah
> order by case when @.sort = 1 then customer_no
> else lastname end
> But if I want the case statement to sort on multiple columns:
> select * from blah
> order by case when @.sort = 1 then customer_no
> else lastname, firstname, customer_no end -- problem
> It doesn't like the lastname, firstname, customer_no part.
> Thanks for any suggestions.
>sql

ORDER BY Bug in SQL Server?

I believe I found a Bug in MS SQL Server 8.0.

I have a query with an ORDER BY clause. The resultset is about 10000 rows.
Sometimes the ordering of the result is not correct, but when I restart the
sqlserver-process, at least the first time the ordering is correct, but then
after executeing the same query a few times, th ordering gets corrupt!

Has anybody encountered a simmilar Problem?

Does anybody know a workaround?

Thank a lot.

Andy Rohr.What do you mean by not correct? Can you post some code and sample data to
reproduce the problem (including the DDL)?

Do the columns in the ORDER BY list represent a unique key? If not, remember
that ORDER BY can produce different valid orderings if the columns are not
unique. For example, with the following query:

SELECT A, B
FROM Sometable
ORDER BY A

the result:
(1,1)
(1,2)

is just as valid as:
(1,2)
(1,1)

Also, in SQLServer ORDER BY columns reference aliased columns in the SELECT
list but can also reference base table columns which do not appear in the
SELECT list, a feature which can cause some confusion.

--
David Portas
----
Please reply only to the newsgroup
--|||> Please reply only to the newsgroup

I repiled to microsoft.public.sqlserver.programmingsql