Sailing_12
asked on
Preserve return order WHERE IN
I am running a SQL query like below, where the list could be any number of ID values in any order. I would like the query to return the values in the exact order they are fed in, not ascending or descending by any column. SQL seems to default to ordering them ascending by the ID - I am looking to override this behavior.
i.e. as written below, I want the returned rows to be ordered:
3106 blah blah
3104 blah blah
3109 blah blah
Is there a way to do thie?
i.e. as written below, I want the returned rows to be ordered:
3106 blah blah
3104 blah blah
3109 blah blah
Is there a way to do thie?
SELECT *
FROM products
WHERE product_id IN (3106, 3104,3109)
>>SQL will not order them
I did not state that well...
I meant, SQL will not order them unless you specify an ORDER BY. Otherwise, it will just take them as they are retrieved. It is the low level database engine that orders them. Without an ORDER BY then ordering is a side effect of the physical storage layer but cannot be relied upon and can change. Always use an order by if you want ordering.
I did not state that well...
I meant, SQL will not order them unless you specify an ORDER BY. Otherwise, it will just take them as they are retrieved. It is the low level database engine that orders them. Without an ORDER BY then ordering is a side effect of the physical storage layer but cannot be relied upon and can change. Always use an order by if you want ordering.
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.
ASKER
Based on your answers, I re-worked my webserver-side code to loop separate queries for each ID in the order they are input into a form.
I found this preferable to the solutions suggested, but thanks for giving me a direction.
I found this preferable to the solutions suggested, but thanks for giving me a direction.
You cannot rely on a specific ordering unless you explicitly specify an order in your SELECT.
If you want to guarantee ordering based on the order of insert, add a TIMESTAMP or AUTOINCREMENT field and order by that.