In Stack Overflow, I saw a poster with a query like this:
select * from student where stud_id in (
And he was frustrated that he couldn't reliably get an ordered output like this:
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.
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:
DECLARE TABLE @StudentIDs
StudentIDKey int IDENTITY(1,1) PRIMARY KEY,
INSERT @StudentIDs (StudentID)
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.