CODEWITHSUNDEEP
javaoracle-databasecollections
user512134
user512134

Reputation: 25

Order retaining in List when retrived from SQL Query

I am getting a list (initial list) from the session which contains the customer Ids in the below order:-

[208700013, 30216118005, 30616118005, 10121005444, 206700013]

Now I am passing these customerIds to the customer table as a collection using "IN" query for which I am getting a list of customerIds in string along with the other values.

But the customerIds are being retrieved in the following order:

10121005444
206700013
208700013
30216118005
30616118005

This is creating a problem when I display the values in the view.

How I can get the same order which is set in the initial list as supposed to the list order returned by the query?

Upvotes: 1

Views: 99

Answers (3)

Nirbhay Mishra
Nirbhay Mishra

Reputation: 1648

you can use this query --

SELECT id FROM table
WHERE id in (10121005444, 206700013, 208700013, 30216118005, 30616118005) 
ORDER BY FIND_IN_SET(id,
    "10121005444, 206700013, 208700013, 30216118005, 30616118005");

second list define the order in which you want your result set to be

Upvotes: 0

Jens Schauder
Jens Schauder

Reputation: 81990

If you only have a hand full of result sets, it might be easiest to sort them in java, using a Comparator.

If you have to do it in oracle you can use a statement like the following:

select *  // never do that in production
from someTable
where id in (10121005444, 206700013, 208700013, 30216118005, 30616118005)
order by decode(id, 10121005444, 1, 206700013, 2, 208700013, 3, 30216118005, 4, 30616118005, 5)

Upvotes: 2

Brian Agnew
Brian Agnew

Reputation: 272337

You can't specify the order using the IN clause. I think you have two options:

  1. perform the query using IN, and sort your result set upon receipt
  2. issue a separate query for each specified id in order. This is obviously less efficient but a trivial implementation.

Upvotes: 0

Related Questions