Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
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?
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?
Who is Participating?
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
for instance
Recordset looks like this
orderid|id|etc...
1 |3 |...
2 |1 |...
3 |2 |...
Select Id, etc...
from recordset
order by orderid
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.
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.
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.
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.
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.
All Courses
From novice to tech pro — start learning today.
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