top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Ranking Functions in SQL Server

+3 votes
606 views

1. Introduction

Ranking functions are useful for providing ranking on the table columns based on the value stored in it. Say for example a table having students and their total marks in a separate column allows you to use the ranking function on the total marks column. With a ranking function you can easily find the second maximum or fourth maximum marks easily in SQL 2005 or in later versions.

The useful ranking functions are displayed below:

  1. ROW_NUMBER()
  2. RANK()
  3. DENSE_RANK()
  4. NTILE()

In this article, we look at how to use those ranking functions by using the NorthWind Sample database.

2. Demo View

First we will create a view from the NorthWnd database that will help with the explanation of the ranking functions. The following is the view:

Create View NetSales as
Select Ord.OrderId, Cat.CategoryName, Prod.ProductName, 
            Ord.UnitPrice * Ord.Quantity as NetSales 
from (Categories cat INNER Join Products prod 
            ON cat.CategoryId = Prod.CategoryId)
      INNER Join [Order Details] Ord 
            ON ord.ProductId = Prod.ProductId;

In the preceding view, we joined the category, products and Order Details tables together to get the required data. Also note that theNetSales is the total sales of a particular product for a given order id. We will perform ranking functions on this Netsales view. The result of the view is shown below. (Note that view does not have the order by clause as it is shown in the select query.)

" target="_blank">ResultRankingFunction.jpg" style="margin:5px; width:534px">
 
3. ROW_NUMBER function

Row number applies to the sequence of numbers for each row. This is useful when you want to delete duplicate rows in a table. The following example shows how the row_number is applied on the ascending ordered netsales:

" target="_blank">ROWNUMBERfunction.jpg" style="margin:5px; width:526px">
 
Here, Rows marked under A, B and C has the same NetSales values. And note that the Row Number is applied sequentially without repititions, even the NetSales values are the same.

4. RANK function

In the following example we used a RANK function to provide a Rank for the products based on the Net Sales it generated. Note that we applied a Rank on NetSales column based on the values it holds in a descending order. So the First Rank is given to the highest net sales and the 2nd Rank is given to Next Highest and so on; see:

" target="_blank">RANKfunction.jpg" style="margin:5px; width:554px">
 
In the preceding example, the set of rows marked in A, B and C have the same NetSales. Note that the same rank is given for the products, which holds the same netsales on a given order. Also note the Gaps in the Rank, say we do not have Rank 2, as there are two products in Rank 1.

The following example shows a different order for each of the Rank and Select Query's statements. Note that the ranking is still applied on the descending order of Netsales and the output is ordered based on the Product Name.

" target="_blank">RANKfunctionOutPut.jpg" style="margin:5px; width:528px"> 
 
5. DENSE_RANK function

The DENSE_RANK function works the same as a rank function and the only difference is that it avoids the gaps in the rank. The following example shows the dense rank in effect:

" target="_blank">DENSERANKfunction.jpg" style="margin:5px; width:587px">
 
Note that even though the Rank 1 is shared between two rows, the next rank given to the NetSales of 10540.00 is 2 not 3. And this is how dense rank differs from the normal rank function. 

6. NTILE function

NTILE function is a normal distribution function. Say for example NTILE (100) means percentile of 100 and when we apply that on the Netsales in Descending orders, the sales is distributed in 100 groups. The top sales in value are placed on percentile 1 and the least sales in value is placed in percentile 100. Say for example if you call NTILE(5) in place of NTILE(100), then you are placing the Netsales in 5 groups stating TOP 5 meaning that RANK 1 group, Rank 2 Group, Rank3 Group, Rank 5 Groups.

In the following example the Netsales is distributed on a 100 percentile:

" target="_blank">NTILEfunction.jpg" style="margin:5px; width:663px">
 
7. Applying Rank Functions within a Group

All the preceding examples used rank functions for the full table of data. But we can use the rank functions for a group and the rank will be reset when the group changes. In our example, we can apply a rank function for Netsales within each category. Doing so will rank the NetSales of product within each category. The following is the example for it:

" target="_blank">RankFunctionswithinaGroup.jpg" style="margin:5px; width:623px"> 

 

 

 

posted Aug 8, 2015 by Shivaranjini

  Promote This Article
Facebook Share Button Twitter Share Button LinkedIn Share Button


Related Articles

SQL Server: Functions

Learn how to create and drop functions in SQL Server (Transact-SQL) with syntax and examples.

What is a function in SQL Server?

In SQL Server, a function is a stored program that you can pass parameters into and return a value.

Create Function

You can create your own functions in SQL Server (Transact-SQL). Let's take a closer look.

Syntax

The syntax to create a function in SQL Server (Transact-SQL) is:

CREATE FUNCTION [schema_name.]function_name
( [ @parameter [ AS ] [type_schema_name.] datatype 
    [ = default ] [ READONLY ]
  , @parameter [ AS ] [type_schema_name.] datatype 
    [ = default ] [ READONLY ] ]
)

RETURNS return_datatype

[ WITH { ENCRYPTION
       | SCHEMABINDING
       | RETURNS NULL ON NULL INPUT
       | CALLED ON NULL INPUT
       | EXECUTE AS Clause ]

[ AS ]

BEGIN

   [declaration_section]

   executable_section

   RETURN return_value

END;

schema_name

The name of the schema that owns the function.

function_name

The name to assign to this function in SQL Server.

@parameter

One or more parameters passed into the function.

type_schema_name

The schema that owns the data type, if applicable.

datatype

The data type for @parameter.

default

The default value to assign to @parameter.

READONLY

It means that @parameter can not be overwritten by the function.

return_datatype

The datatype of the function's return value.

ENCRYPTION

It means that the source for the function will not be stored as plain text in the system views in SQL Server.

SCHEMABINDING

It means that the underlying objects can not be modified so as to affect the function.

RETURNS NULL ON NULL INPUT

It means that the function will return NULL if any parameters are NULL without having to execute the function.

CALL ON NULL INPUT

It means that the function will execute the function even if any parameters are NULL.

EXECUTE AS clause

Sets the security context to execute the function.

return_value

The value returned by the function.

Example

Let's look at an example of how to create a function in SQL Server (Transact-SQL).

The following is a simple example of a function:

CREATE FUNCTION ReturnSite
( @site_id INT )

RETURNS VARCHAR(50)

AS

BEGIN

   DECLARE @site_name VARCHAR(50);

   IF @site_id < 10
      SET @site_name = 'TechOnTheNet.com';
   ELSE
      SET @site_name = 'CheckYourMath.com';

   RETURN @site_name;

END;

This function is called ReturnSite. It has one parameter called @site_id which is an INT datatype. The function returns a VARCHAR(50) value, as specified by the RETURNS clause.

You could then reference the new function called ReturnSite as follows:

USE [test]
GO

SELECT dbo.ReturnSite(8);

GO

Drop Function

Once you have created your function in SQL Server (Transact-SQL), you might find that you need to remove it from the database.

Syntax

The syntax to a drop a function in SQL Server (Transact-SQL) is:

DROP FUNCTION function_name;

function_name

The name of the function that you wish to drop.

Example

Let's look at an example of how to drop a function in SQL Server.

For example:

DROP FUNCTION ReturnSite;

This DROP FUNCTION example would drop the function called ReturnSite.

 READ MORE
SQL Server: User-Defined Functions and Types

Basics:
User defined functions fall into three categories; scalar, inline table-valued, and multi-statement table-valued.

A scalar user-defined function is used to return a single value based on zero or more parameters.

An inline table-valued UDF returns a table data type based on a single SELECT statement that is used to define the returned rows and columns. Unlike a stored procedure, an inline UDF can be referenced in the FROM clause of a query, as well as be joined to other tables. Unlike a view, an inline UDF can accept parameters.

A multi-statement table-valued UDF also returns a result set and is referenced in the FROM clause. Unlike inline table-valued UDFs, they aren’t constrained to use a single SELECT statement within the function definition and instead allow multiple Transact-SQL statements in the body of the UDF definition in order to define a single, final result set to be returned.

Creating Scalar User-Defined Functions:

-- Create a function to check for any suspicious behaviors
-- from the application
CREATE FUNCTION dbo.udf_CheckForSQLInjection
(@TSQLString varchar(max))
RETURNS BIT
AS
BEGIN
DECLARE @IsSuspect bit
-- UDF assumes string will be left padded with a single space
SET @TSQLString = ' ' + @TSQLString
IF (PATINDEX('% xp_%' , @TSQLString ) <> 0 OR
PATINDEX('% sp_%' , @TSQLString ) <> 0 OR
PATINDEX('% DROP %' , @TSQLString ) <> 0 OR
PATINDEX('% GO %' , @TSQLString ) <> 0 OR
PATINDEX('% INSERT %' , @TSQLString ) <> 0 OR
PATINDEX('% UPDATE %' , @TSQLString ) <> 0 OR
PATINDEX('% DBCC %' , @TSQLString ) <> 0 OR
PATINDEX('% SHUTDOWN %' , @TSQLString )<> 0 OR
PATINDEX('% ALTER %' , @TSQLString )<> 0 OR
PATINDEX('% CREATE %' , @TSQLString ) <> 0OR
PATINDEX('%;%' , @TSQLString )<> 0 OR
PATINDEX('% EXECUTE %' , @TSQLString )<> 0 OR
PATINDEX('% BREAK %' , @TSQLString )<> 0 OR
PATINDEX('% BEGIN %' , @TSQLString )<> 0 OR
PATINDEX('% CHECKPOINT %' , @TSQLString )<> 0 OR
PATINDEX('% BREAK %' , @TSQLString )<> 0 OR
PATINDEX('% COMMIT %' , @TSQLString )<> 0 OR
PATINDEX('% TRANSACTION %' , @TSQLString )<> 0 OR
PATINDEX('% CURSOR %' , @TSQLString )<> 0 OR
PATINDEX('% GRANT %' , @TSQLString )<> 0 OR
PATINDEX('% DENY %' , @TSQLString )<> 0 OR
PATINDEX('% ESCAPE %' , @TSQLString )<> 0 OR
PATINDEX('% WHILE %' , @TSQLString )<> 0 OR
PATINDEX('% OPENDATASOURCE %' , @TSQLString )<> 0 OR
PATINDEX('% OPENQUERY %' , @TSQLString )<> 0 OR
PATINDEX('% OPENROWSET %' , @TSQLString )<> 0 OR
PATINDEX('% EXEC %' , @TSQLString )<> 0)
BEGIN
SELECT @IsSuspect = 1
END
ELSE
BEGIN
SELECT @IsSuspect = 0
END
RETURN (@IsSuspect)
END
GO

Creating Inline User-Defined Functions:

CREATE FUNCTION dbo.udf_ReturnEmployeeAddress
( @EmployeeID int)
RETURNS TABLE
AS
RETURN (
SELECT AddressLine1, City, StateProvinceID, PostalCode
FROM Person.Address a
INNER JOIN HumanResources.EmployeeAddress e ON
a.AddressID = e.AddressID
WHERE e.EmployeeID = @EmployeeID )
GO

Creating Multi-Statement User-Defined Functions: Creates a UDF that returns a string array as a table result set

CREATE FUNCTION dbo.udf_ParseArray
( @StringArray varchar(max),
@Delimiter char(1) )
RETURNS @StringArrayTable TABLE (Val varchar(50))
AS
BEGIN
DECLARE @Delimiter_position int
IF RIGHT(@StringArray,1) != @Delimiter
SET @StringArray = @StringArray + @Delimiter
WHILE CHARINDEX(@Delimiter, @StringArray) <> 0
BEGIN
SELECT @Delimiter_position =
CHARINDEX(@Delimiter, @StringArray)
INSERT @StringArrayTable
VALUES (left(@StringArray, @Delimiter_position - 1))
SELECT @StringArray = STUFF(@StringArray, 1,
@Delimiter_position, '')
END
RETURN
END
GO
READ MORE
String functions in SQL

ASCII

Sample :-

DECLARE @CharValue char(5)
SET @CharValue = 'C'
SELECT @CharValue AS 'CHAR', ASCII(@CharValue) AS 'ASCII'
/* Output :-
CHAR ASCII
C 67
*/

--In below code also ASCII function will only consider the leftmost character.
DECLARE @CharValue char(5)
SET @CharValue = 'CODE'
SELECT @CharValue AS 'CHAR', ASCII(@CharValue) AS 'ASCII'

/* Output
CHAR ASCII
CODE 67 */

CHAR

Sample :-

SELECT CHAR(90) AS 'CHAR'

/* Output
CHAR
Z
*/

SELECT CHAR(97) AS 'CHAR'

/* Output
CHAR
a
*/

SELECT CHAR(256) AS 'CHAR'

/* Output
CHAR
NULL
*/

NCHAR

Sample :-

DECLARE @arabicletter nvarchar(10) = N'ك';
SELECT NCHAR(UNICODE(@arabicletter)) AS 'NCHAR', CHAR(UNICODE(@arabicletter)) AS 'CHAR', UNICODE(@arabicletter) AS 'UNICODE'

/* Output
NCHAR CHAR UNICODE
ك NULL 1603
*/

CHARINDEX

Sample :-

DECLARE @StringValue varchar(100);

SELECT @StringValue = 'String Data Types and Functions';
SELECT CHARINDEX('Types', @StringValue) AS 'CHARINDEX'; --Searching for a existent expression
SELECT CHARINDEX('Data', @StringValue, 6) AS 'CHARINDEX'; --Searching from a specific position
SELECT CHARINDEX('Shemeer', @StringValue) AS 'CHARINDEX'; --Searching for a nonexistent expression

/*
The output shows respetively

CHARINDEX

13
8
0
*/

CONCAT

CONCAT ( string_value1, string_value2 [, string_valueN ] )
Where string_value is a string value to concatenate to the other values.

Sample :-

DECLARE @StringValue varchar(100);

SELECT CONCAT('String', ' Data', ' Types' , ' and Functions') AS 'CAPTION';

/*
Output

CAPTION

String Data Types and Functions
*/

DIFFERENCE

Sample :-

SELECT DIFFERENCE('ABC','ABCD'); -- Returns 3, means more similarity
SELECT DIFFERENCE('ABC','ABC'); -- Returns 4, means strong similarity or same
SELECT DIFFERENCE('ABC','123'); -- Returns 0, means no similarity

FORMAT

Sample :-

DECLARE @d DATETIME = '10/01/2011';
SELECT FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result' -- Return 2011/10/1

SELECT FORMAT(101.2, 'C', 'en-us') AS 'Currency Format' -- Returns $101.20

LEFT

Sample :-

SELECT LEFT('SHEMEER',3) -- Returns SHE

SELECT LEFT('SHEMEER',100) -- Returns SHEMEER

--SELECT LEFT('SHEMEER',-1) -- Error 'Invalid length parameter passed to the left function.'

RIGHT

Sample :-

SELECT RIGHT('SHEMEER',3) -- Returns EER

SELECT RIGHT('SHEMEER',100) -- Returns SHEMEER

--SELECT RIGHT('SHEMEER',-1) -- Error 'Invalid length parameter passed to the right function..

LEN

Sample :-

SELECT LEN('SHEMEER') -- Returns 7

LOWER

Sample :-

SELECT LOWER('SHEMEER') -- Returns shemeer

UPPER

Returns a character expression with lowercase character data converted to uppercase. Click here to read more from msdn.

Sample :-

SELECT UPPER('shemeer') -- Returns SHEMEER

LTRIM

Sample :-

The following example uses LTRIM to remove leading spaces from a character string.

SELECT LTRIM(' CODEPROJECT ') -- Returns 'CODEPROJECT '

RTRIM

Sample :-

The following example uses RTRIM to remove trailing spaces from a character string.

SELECT RTRIM(' CODEPROJECT ') -- Returns ' CODEPROJECT'

PATINDEX

Sample :-

SELECT PATINDEX('%those%', 'CODEPROJECT for those who code'); -- Returns 17

QUOTENAME

Sample :-

SELECT QUOTENAME('DESC') -- Returns '[DESC]'
SELECT QUOTENAME('sql[]string') -- Returns '[sql[]]string]' , Notice that the right bracket in the string sql[]string is

doubled to indicate an escape character.

REPLACE

Sample :-

SELECT REPLACE('String Functions','String','Time'); -- Returns 'Time Functions'

REPLICATE

Sample :-

SELECT REPLICATE('0', 3) -- Returns 000
SELECT REPLICATE('0', -1) -- Returns NULL

REVERSE

REVERSE ( string_expression )
string_expression is an expression of a string or binary data type.

Sample :-

SELECT REVERSE('CODEPROJECT') -- Returns 'TCEJORPEDOC'

SOUNDEX

SOUNDEX ( character_expression )
where character_expression is an alphanumeric expression of character data .

Sample :-

SELECT SOUNDEX ('shy'), SOUNDEX ('shi'); -- Returns 'S000

SPACE

SPACE ( integer_expression )
Where integer_expression is a positive integer that indicates the number of spaces. If integer_expression is negative, a null

string is returned.

Sample :-

SELECT CONCAT('CODE',SPACE(2),'PROJCET') -- Returns 'CODE PROJCET'
To include spaces in Unicode data, or to return more than 8000 character spaces, use REPLICATE instead of SPACE.

STR

Sample :-

SELECT 'SL NO' + 1 -- Returns Error
--'Conversion failed when converting the varchar value 'SL NO' to data type int.'

SELECT 'SL NO' + STR(1,2) -- Returns 'SL NO 1'

SELECT STR (FLOOR (123.45), 8, 3); -- Returns ' 123.000'

SELECT STR(123.45, 2, 2); -- Returns '**'
--When the expression exceeds the specified length, the string returns ** for the specified length.

STUFF

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at

the start position and then inserts the second string into the first string at the start position. Click here to read more

Sample :-

SELECT STUFF('CODEFOR THOSE WHO CODE', 5, 0, ' PROJECT ');
-- Returns 'CODE PROJECT FOR THOSE WHO CODE'

--Where 5 is the position where we want to insert ' PROJECT ',
--and 0 represnts the number of characters to be replaced from the position 5

SUBSTRING

Sample :-

SELECT SUBSTRING('CODE PROJECT FOR THOSE WHO CODE', 6, 7);
-- Returns 'PROJECT'

UNICODE

Sample :-

DECLARE @arabicletter nvarchar(10) = N'ك';
SELECT NCHAR(UNICODE(@arabicletter)) AS 'NCHAR', CHAR(UNICODE(@arabicletter)) AS 'CHAR', UNICODE(@arabicletter) AS 'UNICODE'

/* Output
NCHAR CHAR UNICODE
ك NULL 1603
*/

READ MORE
...