SQL Order By removing apostrophe

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

SQL Order By removing apostrophe

Author

Topic

amtdata
Starting Member

3 Posts

Posted - 2005-06-03 : 18:57:41

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

2489 Posts

Posted - 2005-06-03 : 19:15:27

ORDER BY REPLACE(BKTitle, '''', '')

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

amtdata
Starting Member

3 Posts

Posted - 2005-06-04 : 09:55:34

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.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-04 : 10:17:23

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.

shijobaby
Starting Member

44 Posts

Posted - 2009-08-19 : 05:42:14

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources