> devincayce 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.
>
> SELECT CASE idnum
> WHEN '203' then 1
> WHEN '102' then 2
> WHEN '355' then 3
> END AS myorder, m.*
> FROM mytable m
> WHERE idnum IN ('203', '102', '355')
> ORDER BY 1
>
> Short of stored procedures, that is the only way I know that generally
> works with Firebird 1.5 and a non-standard ordering.
>
> HTH,
> Set
>
>
> In your particular case, something like
>
> SELECT * FROM mytable WHERE idnum IN ('203', '102', '355')
> order by cast(substring(idnum from 2 for 1) as integer) -
> cast(substring(idnum from 3 for 1) as integer)
>
> might also work (though I haven't tested it) ;o)
>

What about

SELECT * FROM mytable WHERE idnum = '203'
UNION ALL
SELECT * FROM mytable WHERE idnum = '102'
UNION ALL
SELECT * FROM mytable WHERE idnum = '355'

(ie no ORDER BY)

or does that not return the 3 sets in that order? (I don't know what the
standard, of Firebird's behavior is)

Max