CODEWITHSUNDEEP
sql-server-2008t-sql
user790049
user790049

Reputation: 1254

tsql - how to select 6th row from 1st row and then 6th from 6th row and so on

I have table planets like this:

id  Planet Name
---------------
1   Sun     Sun
2   Moon    Mon
3   Mars    Tue
4   Mercury Wed
5   Jupiter Thu
6   Venus   Fri
7   Saturn  Sat

If I select 'Sun' then I want my query to return

Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars
Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars
Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars
Sun
Venus
Mercury

If I select 'Mon' then I want my query to return

Moon
Saturn
Jupiter
Mars
Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars
Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars
Sun
Venus
Mercury
Moon
Saturn
Jupiter

If I select 'Tue' then I want my query to return

Mars
Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars
Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars
Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars
Sun
Venus

If I select 'Wed' then I want my query to return

Mercury
Moon
Saturn
Jupiter
Mars
Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars
Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars
Sun
Venus
Mercury
Moon
Saturn

If I select 'Thu' then I want my query to return

Jupiter
Mars
Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars
Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars
Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars
Sun

If I select 'Fri' then I want my query to return

Venus
Mercury
Moon
Saturn
Jupiter
Mars
Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars
Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars
Sun
Venus
Mercury
Moon

If I select 'Sat' then I want my query to return

Saturn
Jupiter
Mars
Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars
Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars
Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars

Honestly I am trying to get but I am able achieve this using excel but no idea how to do it in SQL any help much appreciated

Upvotes: 0

Views: 62

Answers (1)

Mike
Mike

Reputation: 3811

I created a SQL Fiddle that builds the sequence you are attempting to build.

The Planets table was slightly altered to have the Sun start at base zero because the next section reads cleaner.

Create Table dbo.Planets 
(
  id int not null primary key
  ,planet nvarchar(20) not null
  ,day nvarchar(3) not null  
);
Go

Insert dbo.Planets
Values
 (0,'Sun','Sun') -- (Not really a planet...)
,(1,'Moon','Mon')
,(2,'Mars','Tue')
,(3,'Mercury','Wed')
,(4,'Jupiter','Thu')
,(5,'Venus','Fri')
,(6,'Saturn','Sat');
-- (...looks like Uranus didn't make the cut.)
Go

The trick is to just use a common table expression for iteration and have an understanding of how modulo (%) works.

With PlanetSequence As
(
  Select id
    ,planet
    ,day
    ,iterations = 0
  From dbo.Planets
  Where day = 'Sun' -- Could pass in a parameter here

  Union All

  -- As the recursive section of this CTE, this is 
  -- responsible for selecting the next item in
  -- in the sequence.
  Select  Planets.id
    ,Planets.planet
    ,Planets.day
    ,iterations = PlanetSequence.iterations + 1
  From dbo.Planets
    Join PlanetSequence
    On (PlanetSequence.id + 5) % 7 = Planets.id
  Where PlanetSequence.iterations < 20
)

Select Planet From PlanetSequence

Result:

PLANET
--------
Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars
Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars
Sun
Venus
Mercury
Moon
Saturn
Jupiter
Mars

Upvotes: 2

Related Questions