SQL: Just like tables, IN clauses have no default order

In Stack Overflow, I saw a poster with a query like this:

And he was frustrated that he couldn't reliably get an ordered output like this:

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:

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.

 

Leave a Reply

Your email address will not be published.