• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 719
  • 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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