Re: Need help Regarding executing select query
Wojtek wrote:
Lew wrote :
It's hard for me to imagine a business case for coercing a SELECT ...
WHERE ... IN clause to match the output order.
To reduce the impact of complex where clauses where you are paginating
the results.
Consider an application which may retrieve 1000+ rows. You do not want
to feed all 1K rows back to a Web app, so you want to be able to show
the first 20, then the next 20, and so on. Maybe let the user select the
range from a drop list (1 - 20 of 1000., 21 - 40 of 1000, ...).
So you run the query with the complex where clause retrieving ONLY the
primary ID column. Store this in an array. It is now trivial to select a
sub-set of the result from the array, use it in a "where in" clause and
get the sub-set rows in the correct order.
Again, SQL does NOT make guarantees about the order of returned results absent
an ORDER BY clause, so in general the described technique will not work.
Furthermore, the semantics of SQL require that the same results be returned
(except for order) regardless of the order of items in the IN set. Taken
together, you cannot have SQL guarantee that the order of a result set matches
the order of items in the IN clause unless you hack the IN clause to match the
ORDER BY clause. In that case it is still the ORDER BY clause that determines
the order of returned results.
Let me state this again: SQL semantics forbid the IN clause from having any
guaranteed effect on the order of returned results.
--
Lew