DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’» is a community of 967,611 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Create account Log in
rajanand ilangovan
rajanand ilangovan

Posted on • Originally published at blog.rajanand.org on

How to generate Permutations in SQL?

Question:

You are planning to go for a summer vacation and finalized the cities you want to visit. But you have not finalized in which order you want to visit them yet.

Write a SQL query to list out all different possible order you can visit these cities.

Note that you don't want to visit the same city again and you don't want to skip any city in your travel plan either.

03-permutations-table.png

Expected output: 03-permutations-output.png

Solution:

Let us create the test data first for the demo.

-- test data
use demo;
if object_id (N'dbo.city', N'u') is not null 
drop table dbo.city
go

create table dbo.city (
    id int identity(1,1),
    city_name varchar(100)
)

insert into dbo.city (city_name) values 
  ('Oslo'),
  ('Helsinki'),
  ('Stockholm'),
  ('Copenhagen')

select id, city_name from dbo.city

Enter fullscreen mode Exit fullscreen mode

This solution is implemented using the Recursive CTE in SQL Server. If you are using other database engines, you can implement it in similar way.

-- solution
declare @total_cities int = (select count(1) from dbo.city);
with travel (travel_path, level) as (
    select cast(city_name as varchar(200)), 
    level = 1 
    from dbo.city
    union all
    select cast(travel.travel_path + ' -> ' + city.city_name as varchar(200)), 
    level = level + 1
    from dbo.city
    inner join travel on level < @total_cities
    where charindex(city.city_name, travel.travel_path) = 0
    )

select 
id = row_number() over(order by travel_path),
travel_path
from travel
where level = @total_cities
order by id

Enter fullscreen mode Exit fullscreen mode

If you like this interview question, you may also like this interview question and answer series.

Top comments (0)

🌚 Life is too short to browse without dark mode