pld51
asked on
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sharath S
How did you split your ID column? Do you have a function to do this?
ASKER
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.
ASKER
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!