[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 718
  • Last Modified:

SQL SELECT - use ORDER BY without a column

Is there a way to order the recordset results of a SELECT using another way than by column?

Specifically, we create SQL Select commands dynamically of the following kind:-
SELECT ID, Title, Country FROM table1 WHERE ID = 3 OR ID = 1 OR ID = 2
The values for ID are entered into one field of a form, separated by a comma, and then the Select command is created by using Split and a loop. At present, the recordset shows the results in order 1, 2, 3. But we want it to show the results in the order of ID entered into the form and thus in the Select command, ie 3, 1, 2 in the case above. Can this be done somehow?
0
pld51
Asked:
pld51
2 Solutions
 
Dale BurrellDirectorCommented:
This is a bit clumsy, but I can't think of a better way right now, in your loop build an order by expression that ends up as

order by case when ID = 3 then 1 else 0 end desc, case when ID = 1 then 1 else 0 end desc, case when ID = 2 then 1 else 0 end desc
0
 
Patrick TallaricoFSEP Systems AnalystCommented:
My suggestion would be to add another field into your recordset to account for the order in which the items were entered.  Then you can fill that field with the proper order when your split and loop segment runs.  you can create the recordset with the fiels, and you should be able to have it present without having to select it for display with the rest of the records.
for instance

Recordset looks like this
orderid|id|etc...
1           |3 |...
2           |1 |...
3           |2 |...

Select Id, etc...
from recordset
order by orderid

0
 
SharathData EngineerCommented:
How did you split your ID column? Do you have a function to do this?
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
pld51Author Commented:
Thanks! The first solution looks easiest to implement - as I did not understand the logic I tried it out as an SQL select command and it ordered the IDs as desired. I now have to build into the loop and test again, but should work.

Second solution should work but is more complicated in terms of adding an intermediate step - updating the new field in the database. And this new field orderid is purely temporary, as the order of the ids will be constantly changed.
0
 
Patrick TallaricoFSEP Systems AnalystCommented:
  It isn't about splitting the ID column, but rather creating another column specifically to store the orderid information.
You will have to adjust your table1 to include this new column.  Once you have added this column, you should be able to add the values into it during your Split/Loop code.  As the code loops through the items, include a segment of code to add the incrementing orderid value.
   This hinges on creating this new field/column in the table that you are pulling the data from, whether it is a temporary table, or a static one.
   
0
 
pld51Author Commented:
Thanks for both solutions, I have now tested Dale Burrell's solution by adjusting the split/loop and it works like a dream. I did not test solution of stpm11 as I don't want to enter another field into the table, but it should work also. Thanks again!
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now