On my database I have the table named
Rank Name Time 1 John 110 2 David 120 3 Bob 121 4 Hans 124 5 Claus 126 6 Gustav 140
I am interested in the person, who has the closest follower.
In the above table David is followed by Bob with a difference of 1.
Hans is followed by Claus with a difference of 2.
I need a SQL-query which returns this:
Name Time-Difference David 1 Hans 2 Bob 3 John 10 Claus 14
Yes, this can be done by joining the table
RankList_T to it self:
select r1.rank,r1.name,r1.time, r2.rank,r2.name,r2.time, r2.time-r1.time as time_diff from GUEST.[RANK_LIST_T] r1 left join GUEST.[RANK_LIST_T] r2 on r1.rank+1=r2.rank where r2.rank is not null order by r2.time-r1.time ;
But this has a very poor performane with an algorithm complexity of O(n^2).
I am looking for a more efficient way to do that on a SQL Database.
Please, check the DDL and DML to reproduce the problem.
SELECT *, LEAD(time) OVER (ORDER BY time) - time AS delta FROM RANK_LIST_T ORDER BY 4;