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! 2 2368
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" <cs*****@yahoo.com> wrote in message
news:3b**************************@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
-- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Neal |
last post by:
I just made a table sortable by column headers by reading a thread
posted here a while ago. Here is the code simplified:
$sort_order = 'id';
if($order == 'jobtitle'){
$sort_order = 'jobtitle';...
|
by: Bhushit Joshipura |
last post by:
This post contains one question and one proposal.
A. May I know why order of evaluation of arguments is not specified in
C/C++?
I asked a question in comp.lang.c++ for the following...
|
by: Rhett Liu |
last post by:
Hi,guys!
I have a table below:
CREATE TABLE rsccategory
(
categoryid NUMERIC(2) IDENTITY(1,1),
categoryname VARCHAR(20) NOT NULL,
PRIMARY KEY(categoryid)
)
Then I do:
|
by: Steven T. Hatton |
last post by:
The following works:
template <typename T>
struct ID3M{
static const T ID;
};
template <typename T>
const T ID3M<T>::ID = {{1,0,0},{0,1,0},{0,0,1}};
|
by: Yannick Turgeon |
last post by:
Hello all,
I'm using SS2K on W2k.
I'v got a table say, humm, "Orders" with two fields in the PK:
OrderDate and CustomerID. I would like to add an "ID" column which
would be auto-increment...
|
by: der |
last post by:
Hello all,
I've a question about order of evaluations in expressions that have && and
|| operators in them.
The question is: will the evalution go left-to-right, no matter what -- even
if the...
|
by: Frank Wallingford |
last post by:
Note: For those with instant reactions, this is NOT the common "why is i
= i++ not defined?" question. Please read on.
I came across an interesting question when talking with my colleagues....
|
by: mdh |
last post by:
May I ask the group the following: (Again, alas , from K&R)
This is part of a function:
while ( ( array1 = array2 ) != '\0' ); /* etc etc */
Is this the order that this is evaluated?
...
|
by: Jeroen |
last post by:
Hi all,
I've got a very specific question about the evaluation order in C++.
Assume some kind of custom array class, with an overloaded subscript
operator. In the following code:
{
my_array...
|
by: bnashenas1984 |
last post by:
Hi every one
I'v been googling for more than 1 week but I don't seem to find any answer for my question. unfortunatly there are not many articles about using Paypal in a self made script.
I...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |