• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

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-
0
ottenm
Asked:
ottenm
  • 2
1 Solution
 
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
 
Scott PletcherSenior 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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now