sorting Conundrum

I have a table that has this as data

aa, Aa and AA

I would like to be able to sort on this row so I get

aa
aa
Aa
Aa
AA
AA

or the reverse, how do i go about this?

SELECT [Arow]
FROM [aTable]
ORDER BY [Arow]


I get them returned in the order they were entered into the db.

Cheers

Fragg

re: sorting Conundrum

Fraggle (Fraggle_Rock_1@yahoo.com) writes:[color=blue]
> I have a table that has this as data
>
> aa, Aa and AA
>
> I would like to be able to sort on this row so I get
>
> aa
> aa
> Aa
> Aa
> AA
> AA
>
> or the reverse, how do i go about this?
>
> SELECT [Arow]
> FROM [aTable]
> ORDER BY [Arow]
>
> I get them returned in the order they were entered into the db.[/color]

You need to use a case-sensitive collation, and your server appears
to have a default collation which is case-insensitive.

You can add a COLLATE clause:

ORDER BY Arow COLLATE Latin1_General_CS_AS

Note: this only works on SQL 2000.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: sorting Conundrum

Erland Sommarskog <sommar@algonet.se> wrote in message news:<Xns93D5DDF151C34Yazorman@127.0.0.1>...[color=blue]
> Fraggle (Fraggle_Rock_1@yahoo.com) writes:[color=green]
> > I have a table that has this as data
> >
> > aa, Aa and AA
> >
> > I would like to be able to sort on this row[/color]
>
> ORDER BY Arow COLLATE Latin1_General_CS_AS
>
> Note: this only works on SQL 2000.[/color]

Thank you, works great.

There must be a book ;)

Fragg

re: sorting Conundrum

In addition to the advice you just got, Standard SQL-92 uses a
case-sensitive collation, so you might also have some problems doing
data exchange with the rest of the world. Try to clean it up if you
can.