Does anyone know how to this:

SELECT * FROM MyTable
ORDER BY name

and to have this ordered in a case INsensitive way?  I couldn't find anything in the manual about this, nor on the entire internet...

> and to have this ordered in a case INsensitive way?

SELECT * FROM MyTable
ORDER BY name COLLATE ANSI_CI

This is if you are using ANSI collation. In general you must append
"_CI" to the collation to indicate case-insensitiveness. for example, I
use the Portuguese collation, so in my case it would be PTG_CI.


>  I couldn't find anything in the manual about this, nor on the entire
internet...

This is SQL 2003 standard ...

--
Fernando Dias
[Team Elevate]

Van

Another little thought even though its not what you asked.

Unless there is a reason to have case sensitive columns I would recommend always using case insensitive collations (eg ANSI_CI) for all VARCHAR and CHAR columns then you don't have to worry about does SMith = Smith = smith.

Roy Lambert

Thanks.  It may be standard, but if it is not written anywhere (not in www.sql-tutorial.com, not in wikipedia,...), how can one know this?  So many
things in ElevateDB are not standard, and the sql manual doesn't mention this as an option:

SELECT [ALL|DISTINCT]
*|<SelectColumn> [,<SelectColumn>]
FROM <SelectTable> [<Join> [,<Join>]|,<SelectTable>]
[WHERE <FilterCondition>]
.....
[ORDER BY <OrderColumn> [,<OrderColumn>]]

Manual section about collations and collation modifiers :
http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=0&topic=5

Technical article about collations and comparisons:
http://www.elevatesoft.com/articles?action=view&articleno=2

--
Fernando Dias
[Team Elevate]

Also, in the same manual page where you saw the SELECT syntax:

«ORDER BY Clause
(...) Each ORDER BY column or expression may optionally include a
COLLATE clause that specifies the collation that should be used for the
ordering (...)»





--
Fernando Dias
[Team Elevate]

Fernando,

<< Thanks.  It may be standard, but if it is not written anywhere (not in
www.sql-tutorial.com, not in wikipedia,...), how can one know this?  So many
things in ElevateDB are not standard, and the sql manual doesn't mention
this as an option: >>

Look at the syntax for OrderColumn:

<OrderColumn> = <ColumnExpression> [COLLATE <CollationName>]
[[ASC|ASCENDING]|[DESC|DESCENDING]]

http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=11&topic=171

In addition to what Fernando indicated, there's also a technical article
about collations:

http://www.elevatesoft.com/articles?action=view&articleno=2

--
Tim Young
Elevate Software
www.elevatesoft.com