Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server function to calculate kth smallest value in a dataset


 
Updated: 6 August 2010
Use SMALL_q to calculate the kth smallest value in a dataset. Use this function to return a value based on its relative standing.
Syntax
SELECT [wctStatistics].[wct].[SMALL_q] (
   <@Values_RangeQuery, nvarchar(4000),>
 ,<@K, float,>)
Arguments
@Values_RangeQuery
the select statement, as text, used to determine the values to be used in the SAMLL_q calculation.
@K
is the position (from the smallest) in the dataset to return. @K is an expression of type float or of a type that can be implicitly converted to float.
Return Types
float
Remarks
·         If @K is less than or equal to zero or @K is greater than the number of rows in the dataset, SMALL_q returns an error.
·         If n is the number of rows in the dataset the @K = 1 returns the smallest value in the dataset and @K = n returns the largest value in the dataset.
·         No GROUP BY is required for this function even though it produces aggregated results.
Examples
CREATE TABLE #s1(
      [num] [float] NOT NULL
)
INSERT INTO #s1 VALUES (1)
INSERT INTO #s1 VALUES (2)
INSERT INTO #s1 VALUES (2)
INSERT INTO #s1 VALUES (2)
INSERT INTO #s1 VALUES (2)
INSERT INTO #s1 VALUES (3)
INSERT INTO #s1 VALUES (4)
INSERT INTO #s1 VALUES (5)
INSERT INTO #s1 VALUES (6)
INSERT INTO #s1 VALUES (7)
INSERT INTO #s1 VALUES (8)
INSERT INTO #s1 VALUES (8)
INSERT INTO #s1 VALUES (8)
INSERT INTO #s1 VALUES (8)
INSERT INTO #s1 VALUES (9)
INSERT INTO #s1 VALUES (10)
INSERT INTO #s1 VALUES (11)
INSERT INTO #s1 VALUES (12)
INSERT INTO #s1 VALUES (13)
INSERT INTO #s1 VALUES (13)
INSERT INTO #s1 VALUES (14)
 

To select the smallest number in the table:

select wct.SMALL_q('SELECT num from #s1', 1)
 

This produces the following result

----------------------
1
 
(1 row(s) affected)

To select the 5th smallest number in the table:

select wct.SMALL_q('SELECT num from #s1', 5)
 

This produces the following result

----------------------
2
 

(1 row(s) affected)



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service