Is "The" your only problem word? What about "A" or "An" and other words that are usually ignored when sorting things like book titles?
I'd be surprised if there was any way to ignore specific words in an ORDER BY; I've been writing SQL for 20+ years and have never seen anything like that. I think what you'll need to do is modify your data so that words like "The", "A", "An", etc. are at the end of the column values. I believe libraries traditionally cataloged books as illustrated in these examples: The Raven ==> Raven, The A Voyage to the Moon and a Trip Around It ==> Voyage to the Moon and a Trip Around It, An Another approach that *might* be easier - if you are using an appropriate version of MySQL - is to create a view that modifies the data for you. You'd need logic like this, which is pseudocode, NOT real SQL: create view view01 as select case when word(subscriber,1) = 'The' then substring(2nd through final words) concatenate 'The' when word(subscriber,1) = 'An' then substring(2nd through final words) concatenate 'An' else subscriber end, other-columns from ... where ... A view can't contain an ORDER BY so you'll have to put the order by in the query that uses the view but that's easy: select subscriber, ... from view01 order by subscriber Rhino ----- Original Message ----- From: "Jack Lauman" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Sunday, June 26, 2005 2:06 PM Subject: ORDER by Question > I'm using a query similar to the following to get an ordered list. > > SELECT.... ORDER BY Subscriber ASC, Name ASC; > > How do I change this so that if the 'Name' field begins with "The " that > the sort begins on the second word? In other words I'd like to be able > to return the word "The" but have it sort on whatever the second word is. > > Thanks, > > Jack > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.323 / Virus Database: 267.8.1/28 - Release Date: 24/06/2005 > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.1/28 - Release Date: 24/06/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
