Sql query for the following situation

Question

Tom loves sports. Every month he takes rest for a while and plays his favorite sport. He changes the game during the month and its change history is shown in table. Challenge: What is the first and last favorite sport played by Tom?

     Tom Favourite Sports
    ----------------------  
Month   PreviousSport   CurrentSport
JAN     REST            CRICKET
JAN     CRICKET         RUGBY
JAN     RUGBY           VOLLEYBALL
JAN     VOLLEYBALL      FOOTBALL
JAN     FOOTBALL        TENNIS
JAN     TENNIS          RUGBY
FEB     REST            KAYAKING
FEB     KAYAKING        SNOWBOARDING
FEB     SNOWBOARDING    SKATING
FEB     SKATING         RAFTING
FEB     RAFTING         KAYAKING
MAR     REST            RACING
MAR     RACING          GLIDING
MAR     GLIDING         SKYDIVING

And the output should be

Month   FirstSport  LastSport
JAN     CRICKET     RUGBY
FEB     KAYAKING    KAYAKING
MAR     RACING      SKYDIVING

CHANGE : Slight modification to the source table

MTH PREVIOUS_SPORT  CURRENT_SPORT
JAN VOLLEYBALL      FOOTBALL
FEB REST            KAYAKING
MAR REST            RACING
JAN CRICKET         RUGBY
FEB SNOWBOARDING    SKATING
MAR RACING          GLIDING
JAN RUGBY           VOLLEYBALL
FEB SKATING         RAFTING
MAR GLIDING         SKYDIVING
JAN FOOTBALL        TENNIS
FEB RAFTING         KAYAKING
JAN TENNIS          RUGBY
JAN REST            CRICKET

Now how do I get the previous output?

Thanks in advance.

Answer 1

I just have considered ID column for Table, we can avoid it & convert Jan/Feb/March as Month Numbers & go ahead without ID column. But for quick reply, This is it !!!

Select Main.[Month],F.CurrentSport,L.CurrentSport from 
(
    Select a.[Month],min(r) fSport ,max(r) lSport from 
        (select * ,ROW_NUMBER() over (Partition by Month order by ID) as R
            from Tom

        )a
    group by a.[Month]
)as Main    
inner join
    (
        select *, ROW_NUMBER() over (Partition by Month order by ID) as R
        from Tom
    )as F on F.R = Main.fSport and f.[Month] = Main.[Month]
inner join
    (
        select *, ROW_NUMBER() over (Partition by Month order by ID) as R
        from Tom
    )as L on L.R = Main.lSport and L.[Month] = Main.[Month]
order by F.ID

See Fiddle DEMO HERE

One more variant, avoiding ROW_Number multiple times & using CTE. I think this should be faster than previous.

;With CTE as
(
select ID,[Month] as M,CurrentSport as Sport 
    , ROW_NUMBER() over (Partition by Month order by ID) as R
from Tom
), CTE1 as 
(
Select M, Min(R) as FS ,Max(R) as LS from  CTE
group by M
)
Select CTE1.M,F.Sport as First,L.Sport as Last from CTE1
inner join CTE as F on F.R = CTE1.FS and F.M = CTE1.M
inner join CTE as L on L.R = CTE1.LS and L.M = CTE1.M
order by L.ID asc

See DEMO HERE

Answer 2

I don't think we need the ID column as such. We can work with RowNumber.

So it should be coming up like this :

SELECT Mintmp.mnth, 
       Mintmp.currentsport AS FirstSport, 
       MaxTmp.currentsport AS CurrentSport 
FROM   (SELECT Row_number() 
                 OVER( 
                   partition BY mnth 
                   ORDER BY mnth) AS RowNum, 
               * 
        FROM   @T1) MinTmp 
       INNER JOIN (SELECT Min(rownum) AS MinRow, 
                          mnth, 
                          Max(rownum) AS MaxRow 
                   FROM   (SELECT Row_number() 
                                    OVER( 
                                      partition BY mnth 
                                      ORDER BY mnth) AS RowNum, 
                                  * 
                           FROM   @T1) tmp 
                   GROUP  BY mnth) grpTable 
               ON grpTable.minrow = MinTmp.rownum 
                  AND grpTable.mnth = MinTmp.mnth 
       INNER JOIN (SELECT Row_number()
                            OVER( 
                              partition BY mnth 
                              ORDER BY mnth) AS RowNum, 
                          * 
                   FROM   @T1) MaxTmp 
               ON Maxtmp.rownum = grpTable.maxrow 
                  AND Maxtmp.mnth = grpTable.mnth