Re: "ORDER BY" problems

Roy Smith wrote:
>
> I've got a table containing 2 columns, title, and sort_title, both
> varchar2's. I want to get a sorted list of titles. That's relatively
> straight forward, I can just do "select title from work order by title",
> but there's a hitch.
>
> For most rows, sort_title is null. But, when it's not null, it's a string
> to be used to establish sorting order instead of title. Thus, title might
> be "The Life and Times of Fred Foobar" and sort_title might be "Life and
> Times of Fred Foobar". Is there any way to say "order by (sort_title if
> it's not null, otherwise by title)"?
>
> The quickest way out of my dilema is to just have every row include a
> value for sort_title, regardless of whether it is the same as title or
> not. That would certainly work, but would waste a lot of space. I'm
> hoping to find a better solution.
>
> --
> Roy Smith <roy_at_popmail.med.nyu.edu>
> New York University School of Medicine
> 550 First Avenue, New York, NY 10016

I'm not sure if DECODE will work in an ORDER BY statement, but try this:

ORDER BY Decode(sort_title,null,title,sort_title)

Martin Bronstein Received on Fri Jul 25 1997 - 00:00:00 CDT