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)