In Stack Overflow, I saw a poster with a query like this:
|
1 2 3 4 5 |
select * from student where stud_id in ( '5', '3', '4' ) |
And he was frustrated that he couldn't reliably get an ordered output like this:
|
1 2 3 4 |
id| name | 5 | John | 3 | Erik | 4 | Michael | |
The problem is that he was assuming that the IN clause had a specified order that was then obeyed by the SELECT statement.
It doesn't.
If you want a SELECT statement to return rows in a particular order, you need to use an ORDER BY clause, and, in this case, you need something to order the rows on in the first place.
I think the easiest way to do that is with a table variable that has an IDENTITY column like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DECLARE TABLE @StudentIDs ( StudentIDKey int IDENTITY(1,1) PRIMARY KEY, StudentID int ); INSERT @StudentIDs (StudentID) VALUES (5), (3), (4); SELECT * FROM Student AS s INNER JOIN @StudentIDs AS id ON s.StudentID = id.StudentID ORDER BY id.StudentIDKey; |
I think that's about the simplest way to do it, particularly if there are a lot of values that could be in that IN clause.
Hope that helps someone.