6 Replies Latest reply: Sep 17, 2012 10:09 AM by Tolls RSS

    Order retaining in List when retrived from SQL Query

    800839
      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
          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
            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
              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
                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
                  >
                  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
                    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.