Complex Order By Logic

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?"

I personally hate this idea, as it would be annoying to scan through. I would put no authors at the end or beginning as 'No Author.'

You can use coalesce to do this:

create table authorTitle
(
author varchar(20),
title varchar(20)
)
insert into authorTitle
select 'Adamson, Jan','Book X'
union
select NULL, 'Bible, The'
union
select 'Wilson, Jonathan','Book Y'
go

select *
from authorTitle
order by coalesce(author,'') + coalesce(title,'')

author title
-- --
Adamson, Jan Book X
NULL Bible, The
Wilson, Jonathan Book Y

You will probably going to want to add this as a computed column, and likely index it to get this to get this to perform well if you have lots of books in the database.

|||Thank you! That did the trick.