This discussion is archived
6 Replies Latest reply: Sep 17, 2012 8:09 AM by Tolls RSS

Order retaining in List when retrived from SQL Query

800839 Newbie
Currently Being Moderated
Hi,

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 here the customerIds which i am retrieving from the DB are in the below order

10121005444
206700013
208700013
30216118005
30616118005

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

Now please clarify how I can get the same order which is set in the initial list as supposed to the list order return by the query?
Because when we pass the list of CustomerIds to query in a particular order it is likely that you will be getting that in different order
which I dont need as I want the same order which I passed to the query , (ie) 208700013, 30216118005, 30616118005, 10121005444, 206700013
when return from the query?

Please clarify.

Thanks.
  • 1. Re: Order retaining in List when retrived from SQL Query
    Tolls Journeyer
    Currently Being Moderated
    Why do you need them in the same order?
    If that's really a requirement then you'll need to order them by hand, as there doesn't seem to be any natural order apparent in the numbers you've given as an example.
  • 2. Re: Order retaining in List when retrived from SQL Query
    848602 Newbie
    Currently Being Moderated
    Consider adding an 'order' column to your table (entity). Ie: rather than a plain integer in your list, have a complex type 'Customer' with 'CustomerID' as one of the field, and 'Order' as the other

    When you do your query from database you can ORDER BY the 'order' column
  • 3. Re: Order retaining in List when retrived from SQL Query
    800839 Newbie
    Currently Being Moderated
    Thanks Tolls, Because i need to display the records in the same order which I have inserted before, that is the requirement. Now please let me know how to retain the same order when I retrieve the List from the Query for which the order will be completely different?
  • 4. Re: Order retaining in List when retrived from SQL Query
    800839 Newbie
    Currently Being Moderated
    The problem is we cannot add any new column to the table which is ruled out. Is there any way we can get the same inserted order? Please clarify.
  • 5. Re: Order retaining in List when retrived from SQL Query
    rp0428 Guru
    Currently Being Moderated
    >
    The problem is we cannot add any new column to the table which is ruled out. Is there any way we can get the same inserted order? Please clarify.
    >
    No - rows in a table are like balls in a basket; there is no FIRST one.

    The ONLY way to ensure the order of data queried is to use an ORDER BY clause on the query. As Tolls said there is no apparent order in this list of values
    >
    208700013, 30216118005, 30616118005, 10121005444, 206700013]
    >
    The order of the values that were returned to you does appear to be in alphabetical order treating the values as VARCHAR2 rather than as numbers
    >
    10121005444
    206700013
    208700013
    30216118005
    30616118005
    >
    All of those above values are sorted alphabetically. If I presented you with that above list of values and ask you to put them into the same order as they were originally how would you do it? You couldn't.

    The only way to sort data like that you provided that has no inherent order is to add an additional SORT_ORDER value.
  • 6. Re: Order retaining in List when retrived from SQL Query
    Tolls Journeyer
    Currently Being Moderated
    OK, what exactly do you mean by "same inserted order"?
    I took your original post to mean the order as shown in the list of numbers supplied to the IN clause.
    If that is the case then you will need to do this by hand, unless there is something else about that data that you haven't told us.

    You could join on a temp table I suppose, which contains the same list and some ordering value.
    But that's little different to doing it by hand in Java.

    If "same inserted order" means the order the rows were inserted into the table then ORDER BY creation date.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points