SQL: select rows with the same order as IN clause
- by Andrea3000
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?
