IF OBJECT_ID('dbo.NTallyFast','IF') IS NOT NULL DROP FUNCTION dbo.NTallyFast; IF OBJECT_ID('samd.lcss8K','IF') IS NOT NULL DROP FUNCTION samd.lcss8K; IF OBJECT_ID('samd.lcssRange8K','IF') IS NOT NULL DROP FUNCTION samd.lcssRange8K; IF OBJECT_ID('dbo.O','IF') IS NOT NULL DROP FUNCTION dbo.O; GO CREATE FUNCTION dbo.O (@L BIGINT,@H BIGINT,@N BIGINT) /***************************************************************************************** [Purpose]: To allow developers to perform a DESCending sort against a CTE tally table without a sort in the execution plan (see below for more details). Given a set of natural numbers from @L to @Hdbo.O(@L,@H,@N) returns the "finite opposite" of @N relative to the set. Traditional opposite numbers (AKA additive inverse numbers) there are two numbers of equal distance from 0; the higher number is a non-negative number and the lower number is the higher number times -1. Ex: the opposite of -10 is 10, the opposite of 4 is -4 and the opposite of 0 is 0. With traditional opposite numbers zero (0) represents the median between each set of opposite numbers. For example, consider the set of numbers between 2 and -2: -2,-1,0,1,2... Here 0 is the median, consider 3 and -3: -3,-2,-1,0,1,2,3... again the median is 0. The median between traditional opposites is always 0 because there an infinite number of positive and negative integers. With this is mind - traditional opposite numbers can be thought of as "infinite opposites". A *Finite* opposite number exists when you define a low and high number. Consider a set of sequential numbers beginning with 6 through 10: (6,7,8,9,10); here the median is 8 and which means the "finite opposite" of 8 is 8, the finite opposite of 7 is 9 and the finite opposite of 6 is 10. With finite opposites the highest number is in the set is always the opposite of the lowest number, the second highest is the opposite of the second lowest, etc. [Author]: Alan Burstein [Compatibility]: SQL Server 2005+ [Syntax]: --===== Autonomous SELECT O.Op FROM dbo.O(@L,@H,@N) AS O; --===== Against a table using APPLY SELECT t.col1, t.col2, t.col3, O.O FROM dbo.someTable AS t CROSS APPLY dbo.O(t.col1,t.col2,t.col3) AS O; [Parameters]: @L = INT; The lower boundary of the set @H = INT; The upper boundary of the set @N = INT; The number to evaluate relative to the set beginning with @L & ending with @H [Returns]: inline table valued function returns: Op = Int; The "Finite Opposite" of @N [Dependencies]: NA [Developer Notes]: When @L and @H are infinate opposites then the finite and infinate opposite values are the same. The magic of the function is that there are no wrong numbers. @high does not have to be lower than @low and @N does not have to be between @low and high. 1. Returns NULL when @L, @H or @N are NULL. 2. dbo.O is deterministic. For more deterministic functions see: https://msdn.microsoft.com/en-us/library/ms178091.aspx [Examples]: --===== 1. -- (1.1) ; -- (1.2) ; --===== 1. -- (1.1) ; [Revision History]: ----------------------------------------------------------------------------------------- Rev 00 - 20180000 - Initial Development - Alan Burstein Rev 01 - 20181221 - Initial Development - Alan Burstein *****************************************************************************************/ RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT Op = @L+@H-@N; GO