Ok, another SQL trick. How to get a row set where the rows are ordered by a specific value sequence of a particular column?

This is not a very common problem, but if you happen to need something like this, you may scratch your head for a couple of minutes before you figure it out.

So, the challenge is the following, you have a table like the one below:

 

If you want to return all rows with ids 4, 5, 7 and 8  in order by any column, you can do so easily using order by [the_column] asc or desc; but if you want to return those same rows in a chaotic desired order is no so trivial.

Say we want the rows ids 4, 5, 7 and 8 and we want them such that the ids appear in the following order: 7, 4, 8 and then 5

Before looking at how to do it, challenge yourself and try to come up with that query.

Ok now let’s look at the solution. It involves the use of the CHARINDEX string function. CHARINDEX returns the starting index of parameter1 in the string given on parameter2, if parameter1 is a substring of parameter2. For example:

[sourcecode language="sql"] SELECT CHARINDEX ( '23', 'AA23BB') --returns 3 SELECT CHARINDEX ( 'abc', 'aapppabcbc') --returns 6 [/sourcecode]

That means we can do something similar with a set of numbers when treated as a strings, like so:

[sourcecode language="sql"] SELECT CHARINDEX ( '7', '7,4,8,5') --returns 1 SELECT CHARINDEX ( '4', '7,4,8,5') --returns 3 SELECT CHARINDEX ( '8', '7,4,8,5') --returns 5 SELECT CHARINDEX ( '5', '7,4,8,5') --returns 7 [/sourcecode]

Now you see how easy is toget order range using this style. Finally, we can create our query using this technique and return our rows in the order we want.

[sourcecode language="sql"] select * from Person where PersonID in (4,5,7,8) order by CHARINDEX(CONVERT(varchar, Person.PersonID), '7,4,8,5') [/sourcecode]

Happy coding!

2 Comments