4
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.

Brian M Stafford
  • 8,483
  • 2
  • 16
  • 25
user1928432
  • 133
  • 1
  • 9
  • which database you are using? – user1844933 Feb 09 '23 at 15:57
  • this QA may solve you problem https://stackoverflow.com/a/15072297/1844933 – user1844933 Feb 09 '23 at 16:01
  • @user1844933 I using access 2007, thank you. – user1928432 Feb 09 '23 at 16:02
  • 1
    Not a vb6 issue. Access (perhaps more specifically the Jet or ODBC drivers, whatever you're using) doesn't support `charindex` (thats a SQL Server function, and perhaps other databases). The `InStr` function might work - see [this MS Access doc](https://support.microsoft.com/en-us/office/instr-function-85d3392c-3b1c-4232-bb18-77cd0cb8a55b). – MarkL Feb 09 '23 at 17:02

1 Answers1

4
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
Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • 1
    it works! this help me much time, can not find answer on google for hours, thank you very much, best regards. – user1928432 Feb 10 '23 at 13:29