SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 SQL Order By removing apostrophe
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

amtdata
Starting Member

3 Posts

Posted - 06/03/2005 :  18:57:41  Show Profile  Reply with Quote
I am using a standard order by SQL clause
SELECT BKTitle, Author ORDER BY BKTitle however a lot of the book titles are enclosed in apostrophes e.g. 'New Raiments of Self' appears before - A Guide to Retail Success. How can I return this list alphabetically ignoring any leading apostrophes.

MichaelP
Jedi Yak

USA
2489 Posts

Posted - 06/03/2005 :  19:15:27  Show Profile  Visit MichaelP's Homepage  Reply with Quote
ORDER BY REPLACE(BKTitle, '''', '')

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

amtdata
Starting Member

3 Posts

Posted - 06/04/2005 :  09:55:34  Show Profile  Reply with Quote
That works but only if I remove my DISTINCT clause otherwise I get

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Apologies I didn't specify this. We have a site with a database format controlled by another system for publishing our books. Each title is listed twice, once for hardback and once for paperback so we added DISTINCT so that the book list did not contain two of everything.
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/04/2005 :  10:17:23  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
SELECT Distinct BKTitle, Author From <yourTable> ORDER BY 1
or worst case
Select BKTitle, Author From (Select Distinct BKTitle, Author From <yourTable>) A Order By 1

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.
Go to Top of Page

shijobaby
Starting Member

India
44 Posts

Posted - 08/19/2009 :  05:42:14  Show Profile  Visit shijobaby's Homepage  Reply with Quote
Hi

The reasons and ways to avoid this error have discussed in this

site with good examples. By making small changes in the query

http://sqlerror104.blogspot.com/2009/08/order-by-items-must-appear-in-select_19.html
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000