Hi-

I've got some issues with the ordering of search results. I've got 4
fields, (1, 2, 3, 4). Most often there is data in field 1. Sometimes
there isn't and so then I'd like to look at field 2.

I can accomplish most of what I want with this:

select
1 as orderField,
ISNULL(1, 2) AS orderField,
from
table
order by
orderField


Of course there are occassions when sometimes both 1 and 2 are blank
and so I want to use whatever value is in 3 or 4, but the results need
to all be in alphabetcal order.

here's some sample data:


record 1: field 1 - apple
record 2: field 1 - peach
record 3: field 2 - orange
record 4: field 3 - banana
record 5: field 4 - grape


so with the current query, the order would be:
banana, grape, apple, orange, peach

I need it to be in alphabetical order no matter which of the 4 fields
it came from:
apple, banana, grape, orange, peach


Is this possible?

Thanks!

Hi

Check out the coalesce function in books online.

SELECT Col1, Col2, Col3, Col4
FROM MyTable
ORDER BY COALESCE ( Col1, Col2, Col3, Col4 )

John

"Cindy" <cschall@yahoo.com> wrote in message
news:3b69843f.0407102315.485600c5@posting.google.c om...
> Hi-
>
> I've got some issues with the ordering of search results. I've got 4
> fields, (1, 2, 3, 4). Most often there is data in field 1. Sometimes
> there isn't and so then I'd like to look at field 2.
>
> I can accomplish most of what I want with this:
>
> select
> 1 as orderField,
> ISNULL(1, 2) AS orderField,
> from
> table
> order by
> orderField
>
>
> Of course there are occassions when sometimes both 1 and 2 are blank
> and so I want to use whatever value is in 3 or 4, but the results need
> to all be in alphabetcal order.
>
> here's some sample data:
>
>
> record 1: field 1 - apple
> record 2: field 1 - peach
> record 3: field 2 - orange
> record 4: field 3 - banana
> record 5: field 4 - grape
>
>
> so with the current query, the order would be:
> banana, grape, apple, orange, peach
>
> I need it to be in alphabetical order no matter which of the 4 fields
> it came from:
> apple, banana, grape, orange, peach
>
>
> Is this possible?
>
> Thanks!

You can use COALESCE:

.... ORDER BY COALESCE(col1, col2, col3, col4)

Be aware that the columns will all be cast to a single datatype (the
datatype of highest precedence among them) so they must all be of compatible
types. If the columns are not of the same datatype then the conversion may
affect the ordering, which may differ from what you expect.

--
David Portas
SQL Server MVP
--