How to retain the order of results while using a IN Clause in DB2?

Question

I need to get a set of results using the IN clause, but the default ordering is done and the results are returned. Is there a way to maintain the order of the in clause in db2 ?

ORDER BY FILED would be a solution in MySQL but is there an equivalent in DB2 ?

Thanks for the reply.ORDER BY? The list is dynamic in every case. I invoke it via a prepared statement. Can you give me an example? — Sukanya, Mar 06 '13 at 05:24
SELECT POST_ID,POST_TITLE FROM BLOG_POST WHERE POST_ID IN (68,76,72,73); This is the requirement, I need to get the results in the same order — Sukanya, Mar 06 '13 at 05:27

score 0 · Answer 1 · edited May 23 '17 at 10:32

0

As I understand it, you want to do this:

select foo from table where bar in (3, 1, 2);

and order by which item bar matched. i.e. bar = 3 comes first, followed by 1, then 2.

I don't think there is a built-in way to do what you want in DB2.

However, take a look at this recent question, which discusses workarounds.

edited May 23 '17 at 10:32

Community

1
1

answered Mar 06 '13 at 09:03

score 0 · Answer 2 · answered Mar 19 '14 at 14:24

If you want results in a particular order, ORDER BY is how to do it. SQL does not guarantee an order unless you use ORDER BY. There is no relationship whatsoever between a sort order of a result set and the way you choose to list items in any IN() clause. An IN() clause has nothing to do with it.

Note that a specific sort order may be obtained at any time without ORDER BY purely by luck. However, it is not guaranteed. If rows change over time, a different sort order might show up without warning.

This is a SQL behavior, not DB2. DB2 simply works for this behavior the way SQL is intended to work.

How to retain the order of results while using a IN Clause in DB2?

2 Answers2