what comes after 'Z'? (for varchar order by)

I've been hunting around for a character to put at the beginning of a varchar field that would cause it to alphabetize at the end of the list in an order by.  The field is defined as:
[MyField] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

And everything I have tried that comes after 'z' (lower case, which comes after upper case using an ascii table) gets sorted to the top instead of the bottom.  This includes the tilde, and broken and unbroken pipe character, both curly braces, and a couple of extended set characters way down on the list.

Thanks for any help-
ottenmAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
True, it's ugly.

You could make the main query a sub-query, with a TOP 100 PERCENT to allow the ORDER BY in the subquery, then do a replace in an outer query to get rid of the CHAR(255) -- yuck.
0
 
Scott PletcherSenior DBACommented:
Hmm, odd, those apparently should work.

If you don't care what the char looks like, I suggest CHAR(255).
0
 
ottenmAuthor Commented:
I thought so, too.  

The 255 works, but it is pretty ugly (hence the attempts with tilde, etc.).  I could code around it, but would prefer to find out why it's happening.  Maybe something with the COLLATE attribute?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.