Very Computer

by **Mitchell Lyon** » Tue, 29 Feb 2000 04:00:00

I was wondering if there is anyway to Keep you order when using the IN
statement.. For Example.. I have an ID Field and i want to grab a a certain
number of fields.. But it is important that i keep the order in which the ID
fields are in the Array list.. something like this Select * From Customer
where IN (1, 6, 2, 5, 10).. what is happening is the results are coming
back to me in order of the ID field not the order i have in the IN list.. is
there any way or some syntax that i am not aware of that will keep the
results in the order i ask!! Thanks..

by **Adi Coh** » Tue, 29 Feb 2000 04:00:00

There isn't a way to do it with one call. If the order is important for you
and it is based on another filed then you can add - order by FieldName.

Quote:> I was wondering if there is anyway to Keep you order when using the IN
> statement.. For Example.. I have an ID Field and i want to grab a a
certain
> number of fields.. But it is important that i keep the order in which the
ID
> fields are in the Array list.. something like this Select * From Customer
> where IN (1, 6, 2, 5, 10).. what is happening is the results are coming
> back to me in order of the ID field not the order i have in the IN list..
is
> there any way or some syntax that i am not aware of that will keep the
> results in the order i ask!! Thanks..

by **Patrick Loga** » Tue, 29 Feb 2000 04:00:00

Ordering the arguments in an IN clause doesn't make since. In Boolean
logic, " 5 OR 6 OR 7" is equivalent to "6 OR 5 OR 7" unless somekind of
precendence operator is used which in an IN clause still would make much
since. Remember an IN clause is basically shorthand for a bunch or OR
clauses.

Just ORDER BY your results if possible.

--
Patrick Logan, MCSD
Senior Technical Development Advisor
McKessonHBOC -- Extended Care Solutions Group
(417) 874-4000 ext. 4424

by **rsar..** » Tue, 29 Feb 2000 04:00:00

Quote:> I was wondering if there is anyway to Keep you order when using the IN
> statement.. For Example.. I have an ID Field and i want to grab a a
certain
> number of fields.. But it is important that i keep the order in which
the ID
> fields are in the Array list.. something like this Select * From
Customer
> where IN (1, 6, 2, 5, 10).. what is happening is the results are
coming
> back to me in order of the ID field not the order i have in the IN
list.. is
> there any way or some syntax that i am not aware of that will keep the
> results in the order i ask!! Thanks..

If you will see the query plan for your SQL you will see that it is
converted into bunch id = 1 or id = 6 or id = 2 ..etc .. now this
query plan is excuted and the results are returned .. to get the result
in any specific order you have to specify the order by column ..

The result you sought requires a cursor .. or some more line of code, I
dont think you can get it in one select statement ..

sarosh
--
http://www.manyQuestions.com
FREE sample certification Exams, FAQs, Code Samples, interview
Questions, job search, resume writing etc.

Sent via Deja.com http://www.deja.com/
Before you buy.

by **rsar..** » Tue, 29 Feb 2000 04:00:00

Quote:> I was wondering if there is anyway to Keep you order when using the IN
> statement.. For Example.. I have an ID Field and i want to grab a a
certain
> number of fields.. But it is important that i keep the order in which
the ID
> fields are in the Array list.. something like this Select * From
Customer
> where IN (1, 6, 2, 5, 10).. what is happening is the results are
coming
> back to me in order of the ID field not the order i have in the IN
list.. is
> there any way or some syntax that i am not aware of that will keep the
> results in the order i ask!! Thanks..

If you will see the query plan for your SQL you will see that it is
converted into bunch id = 1 or id = 6 or id = 2 ..etc .. now this
query plan is excuted and the results are returned .. to get the result
in any specific order you have to specify the order by column ..

The result you sought requires a cursor .. or some more line of code, I
dont think you can get it in one select statement ..

sarosh
--
http://www.manyQuestions.com
FREE sample certification Exams, FAQs, Code Samples, interview
Questions, job search, resume writing etc.

Sent via Deja.com http://www.deja.com/
Before you buy.

by **Rafat Saros** » Wed, 01 Mar 2000 04:00:00

> > I was wondering if there is anyway to Keep you order when using the
IN
> > statement.. For Example.. I have an ID Field and i want to grab a a
> certain
> > number of fields.. But it is important that i keep the order in
which
> the ID
> > fields are in the Array list.. something like this Select * From
> Customer
> > where IN (1, 6, 2, 5, 10).. what is happening is the results are
> coming
> > back to me in order of the ID field not the order i have in the IN
> list.. is
> > there any way or some syntax that i am not aware of that will keep
the
> > results in the order i ask!! Thanks..

> If you will see the query plan for your SQL you will see that it is
> converted into bunch id = 1 or id = 6 or id = 2 ..etc .. now this
> query plan is excuted and the results are returned .. to get the
result
> in any specific order you have to specify the order by column ..

> The result you sought requires a cursor .. or some more line of code,
I
> dont think you can get it in one select statement ..

> sarosh
> --

OK, Here is another thought from my friend Jagadish Prasana

SELECT id, case id when 2 then 1
when 1 then 2
when 3 then 3
else 0
end AS ORDER_1
FROM tablename
WHERE id IN (2,1,3)
ORDER BY ORDER_1;

Rafat Sarosh
---------------------------------------
http://www.manyQuestions.com
FREE sample certification Exams, FAQs, Code Samples, interview
Questions, job search, resume writing etc.

Sent via Deja.com http://www.deja.com/
Before you buy.

Very Computer

IN Statement

IN Statement

IN Statement

IN Statement

IN Statement

IN Statement

IN Statement