SEO

#1 (**permalink**) 02-28-2008, 05:27 PM

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

#2 (**permalink**) 02-28-2008, 05:27 PM

Fraggle (Fraggle_Rock_1@yahoo.com) writes:
> 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.

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

#3 (**permalink**) 02-28-2008, 05:27 PM

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

Thank you, works great.

There must be a book

Fragg

#4 (**permalink**) 02-28-2008, 05:28 PM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

sorting Conundrum