SQL: select rows with the same order as IN clause

Posted by Andrea3000 on Stack Overflow See other posts from Stack Overflow or by Andrea3000
Published on 2012-03-23T23:04:24Z Indexed on 2012/03/23 23:30 UTC
Read the original article Hit count: 257

Filed under:

sql

|

sql-server

|

ms-access

|

order-by

|

in-clause

I know that this question has been asked several times and I've read all the answer but none of them seem to completely solve my problem.

I'm switching from a mySQL database to a MS Access database with MS SQL. In both of the case I use a php script to connect to the database and perform SQL queries.

I need to find a suitable replacement for a query I used to perform on mySQL. I want to:

perform a first query and order records alphabetically based on one of the columns
construct a list of IDs which reflects the previous alphabetical order
perform a second query with the IN clause applied with the IDs' list and ordered by this list.

In mySQL I used to perform the last query this way:

SELECT name FROM users WHERE id IN ($name_ids) ORDER BY FIND_IN_SET(id,'$name_ids')

Since FIND_IN_SET is available only in mySQL and CHARINDEX and PATINDEX are not available from my php script, how can I achieve this?

I know that I could write something like:

SELECT name 
  FROM users 
 WHERE id IN ($name_ids) 
ORDER BY CASE id
           WHEN ... THEN 1 
           WHEN ... THEN 2
           WHEN ... THEN 3
           WHEN ... THEN 4
         END

but you have to consider that:

IDs' list has variable length and elements because it depends on the first query
that list can easily contains thousands of elements

Have you got any hint on this? Is there a way to programmatically construct the ORDER BY CASE ... WHEN ... statement? Is there a better approach since my list of IDs can be big?

Developer IT