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
 Old Forums
 CLOSED - General SQL Server
 Advanced Order By Logic
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

cominginsecond
Starting Member

2 Posts

Posted - 02/20/2007 :  22:42:31  Show Profile  Visit cominginsecond's Homepage
I apologize if this is not the appropriate forum for this question.

I've written a searchable database-driven application in classic ASP and vbscript with a SQL Server backend. What I need to do is this: order the results of a query so that if the first "order by" field is null to order that entry based on the second "order by" field.

The application I am writing is a database of books, and my client wants the results to be ordered by Author, unless there is no Author, in which case he wants that entry ordered by book title. Here is an example of how he wants the books sorted:

Adamson, Jan - Book X
Bible, The
Wilson, Jonathan - Book Y

I hope I've explained the scenario correctly. What's the best way to write a SQL statement that will yield the ordering criteria described above? Also, is there any way to get "Order by" to ignore articles like "A" and "The?"

khtan
In (Som, Ni, Yak)

Singapore
17438 Posts

Posted - 02/20/2007 :  22:47:00  Show Profile
quote:
What I need to do is this: order the results of a query so that if the first "order by" field is null to order that entry based on the second "order by" field.

like this ?

order by coalesce(Author, Title), Title


quote:

Also, is there any way to get "Order by" to ignore articles like "A" and "The?"


try this, use replace() to remove the 'The ' and 'A '

order by replace(replace(Title, 'The ', ''), 'A ', '')



KH

Go to Top of Page

cominginsecond
Starting Member

2 Posts

Posted - 02/21/2007 :  13:49:23  Show Profile  Visit cominginsecond's Homepage
Thank you! I'll give these a shot.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 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