Subject Re: [firebird-support] Order By with In Operator Question
Author Helen Borrie
At 04:34 AM 3/10/2006, you wrote:
>This is a general sql question, but if I have a query like this:
>SELECT * FROM mytable WHERE idnum IN ('203', '102', '355')
>How do I get the returned recordset to be in the order of the
>records with the IDs listed in the
>"IN" statement like this
>Record 1 idnum = 203
>Record 2 idnum = 102
>Record 3 idnum = 355
>When I have tried it so far I get an order other than that.

The IN() predicate isn't an ordering mechanism, it's a row selection
mechanism. In SQL, the order of a set is determined only by an
appropriate ORDER BY clause.

If you want the user's ad hoc selection to determine the ordering of
the rows returned, then probably the only way you'll get that with a
dynamic selection is to concoct a UNION statement in run-time on the
client side.

An alternative would be to write a selectable stored procedure that
takes an input string of sufficient length to avoid overflows,
consisting of a parseable list of the idnums which you can process in
a loop and pass out the rows one-by-one using a SUSPEND at the end
of the loop iteration. Although this isn't "basic PSQL", it is
doable. You would write one executable procedure that you call to
progressively process your list, chopping off the "head" of the list
and passing back the "head" value and the remnant each time,
RETURNING_VALUES() into variables.