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.