|Subject||Re: [firebird-support] Order By with In Operator Question|
> devincayce wrote:What about
>> 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.
> 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)
SELECT * FROM mytable WHERE idnum = '203'
SELECT * FROM mytable WHERE idnum = '102'
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)