select * from [main] where id in (33,11,22) order by id
if i using this query, i will get the recordset order by this:
11
22
33
but i want to the return recordset by this order (same as id in (33,11,22) order):
33
11
22
by google i understand we can use this to realize:
select * from [main] where id in (33,11,22) order by charindex(ltrim(id),'33,11,22')
but the problem is vb6 does not support charindex, I got the error The expression contains undefined function call CHARINDEX when I using it.
so what can I do in vb6? thank you.
update: by @MarkL's suggest, I'm using instr to instead of CHARINDEX, the query running but not running as expectation, the return recordset with this order:
33
22
11
thanks.
select * from [main] where id in (33,11,22) order by InStr("33,11,22", id)
Should give you what you want.
A better version to guard against ID 11 matching "111,222" would be:
select * from [main] where id in (33,11,22) order by InStr(",33,11,22,", "," & id & ",")
A set based alternative would be to have a 2nd "matches" table with a AutoNumber RowNum column & a Value column into which you insert 33, 11, 22 (in order) allowing for:
select * from [main] inner join matches on (matches.id = [main].id) order by rownum