Generating SQL Permutations

Kevin Feasel



Michael J. Swart uses a recursive common table expression and bit shifting to build a full set of permutations:

If you google “generating permutations using SQL”, you get thousands of hits. It’s an interesting problem if not very useful.
I wrote a solution recently and thought I’d share it. If you’re keen, try tackling it yourself before moving on.

Click through for the script.  It’s an interesting approach and might be worth playing a round of code golf.

Related Posts

The Birthday Problem

Mala Mahadevan explains the Birthday problem and demonstrates it with SQL and R: Given a room of 23 random people, what are chances that two or more of them have the same birthday?  This problem is a little different from the earlier ones, where we actually knew what the probability in each situation was. What are chances […]

Read More

Building A Recommendation System With Graph Data

Arvind Shyamsundar and Shreya Verma show how to implement a recommender system using SQL Server 2017’s new graph database functionality: As you can see from the animation, the algorithm is quite simple: First, we identify the user and ‘current’ song to start with (red line) Next, we identify the other users who have also listened […]

Read More


February 2017
« Jan Mar »