Microsoft SQL Server - Lesson 39: Ranking Records Over Partitions

Ranking the Records

Introduction

Consider the following SQL statement:

USE LambdaPropertiesManagement1;
GO
SELECT props.Bedrooms,
       props.MonthlyRate
FROM Listing.Properties props
ORDER BY props.MonthlyRate;
GO

This produces:

The goal of the above statement was to produce the list of properties arranged by the monthly rate from the least expensive up. What is missing is a column that clearly shows such a ranking as 1 (for the first group), 2 (for the second group), and so on. As another example, if we have groups of records, we may want to know from one record starts a cetain category and where that category ends. Transact-SQL provides functions that can be used to rank records. Those functions can be combined with other functions, such as aggregates, to get more statistics. The functions used to rank groups of records are call ranking functions.

Practical Learning: Introducing Record Ranking

Start Microsoft SQL Server and connect
Right-click the name of the server and click New Query
Right-click the name of the server and click Start PowerShell
Type SQLCMD and press Enter
To see the list of properties from the LambdaPropertiesManagement1 database (the database was created in the previous lesson), type the following code and press Enter after each line (if your PowerShell is not working, type the code in the Query Editor and press F5 to execute):
```
USE LambdaPropertiesManagement1;
GO

SELECT props.Bedrooms,
       props.MonthlyRate
FROM Listing.Properties props
ORDER BY props.MonthlyRate;
GO
```

Getting the Row Number in Each Group

If you have a list of records and you make a selection from it. The records would appear in the same order they were created. Sometimes, when the records display, if they were not created with an identity column or a sequence, you may want a column that shows the incremental sequence of the records. Transact-SQL is equipped with a function named ROW_NUMBER that displays the records, each with an integer that shows its ordered position. The syntax of the ROW_NUMBER() function is:

ROW_NUMBER() 
    OVER([PARTITION BY value_expression, ... [ n ] ] order_by_clause) RETURNS bigint

The ROW_NUMBER() function takes no argument. It is followed by a call to OVER(). If you call the parameter-less OVER(), the incrementing numbers of the records would be displayed. If you want to display records in groups, create a partition passed to OVER(). In this case, the incrementing numbers of each group would display.

Practical Learning: Showing the Row Number of Each Record in Each Group

Switch to the Query Editor and type the following code:

USE LambdaPropertiesManagement1;
GO

SELECT props.PropertyType Type,
       props.MonthlyRate Rate,
       ROW_NUMBER() OVER(ORDER BY props.PropertyType) Rank
FROM Listing.Properties props;
GO

To execute, right-click inside the Query Editor and click Execute

Notice the incrementing value of each record in the Rank column.
To create the groups and show the incrementing numbers in each group, change the statement as follows:

USE LambdaPropertiesManagement1;
GO

SELECT props.PropertyType Type,
       props.MonthlyRate Rate,
       ROW_NUMBER() OVER(PARTITION BY props.PropertyType ORDER BY props.PropertyType) Rank
FROM Listing.Properties props;
GO

To execute, press F5.
Notice that, this time, each group has its own incrementing sequence:

To get the incrementing number of properties based on the city, change the statement as follows:

USE LambdaPropertiesManagement1;
GO
SELECT props.PropertyType Type,
       props.MonthlyRate Rate,
       props.City,
     ROW_NUMBER() OVER(PARTITION BY props.City ORDER BY props.PropertyType) [Row #]
FROM Listing.Properties props;
GO

To execute, press F5
Click inside the Query Editor, press Ctrl + A, and press Delete

Ranking the Records Over

If you have a column with repeating values, you can get a list of the repeating values and rank them as 1 for the first category, 2 for the second, and so on. To support this, Transact-SQL provides a ranking function named RANK. Its syntax is:

RANK() OVER([partition_by_clause] order_by_clause) RETURNS bigint;

This function takes no argument. It is followed by calling OVER(). The RANK() function is used to show from what record to what record a (or each) group starts. If the order of records is messy, the function cannot perform its function. After all, imagine you have a list of properties as Apartment, Townhouse, Apartment, Single Family, Single Family, Townhouse, Apartment, Single Family.

There is no way you can determine where a group starts and where it ends. For this reason, the RANK() function requires that the records be arranged, which is done by adding an ORDER BY clause. The arrangement is passed to OVER().

Before arranging the records, you can ask the database engine to create partitions. Although you can use any column of your choice, you should use a column that is in the SELECT statement.

Practical Learning: Ranking the Records Over

To see a simple example of calling the RANK() function, type the following code:

USE LambdaPropertiesManagement1;
GO

SELECT PropertyType [Property Type],
       RANK() OVER(ORDER BY PropertyType) [Ranking by Type]
FROM Listing.Properties;
GO

To execute, right-click inside the Query Editor and click Execute
This result shows that the first category, Apartment, starts from record #1 to record #44 (= 45 - 1). The next category starts on record #45 to record #59 (= 60 - 1), and so on. Also notice that, by default, the RANK() function produces an instance of each record.
With this type of result, you can also include the ROW_NUMBER() function to show a sequence of the records in each group. To see an example, change the statement as follows:
```
USE LambdaPropertiesManagement1;
GO

SELECT props.PropertyType Type,
       props.MonthlyRate Rate,
       RANK() OVER(ORDER BY PropertyType) [Ranking by Type],
       ROW_NUMBER() OVER(PARTITION BY props.PropertyType ORDER BY props.PropertyType) Rank
FROM Listing.Properties props;
GO
```
To execute, right-click inside the Query Editor and click Execute
Click inside the Query Editor, press Ctrl + A, and press Delete.
Switch to the PowerShell window

To show each unique category and the result of the statistics, type the following code and press Enter after each line:

SELECT DISTINCT(PropertyType) [Property Type],
       RANK() OVER(ORDER BY PropertyType) [Ranking by Type]
FROM Listing.Properties;
GO

Notice that we used only one column. You can use as many columns as you want. In fact, you can select one or more different columns and arrange the records based on one of those columns.
Switch to the Query Editor and type the following code:
```
USE LambdaPropertiesManagement1;
GO

SELECT props.PropertyType "Property Type",
       props.MonthlyRate Rate,
       RANK() OVER(ORDER BY props.MonthlyRate) "Ranking"
FROM Listing.Properties props;
GO
```
Press F5 to execute
Notice that, this time, the groups are ranked by the monthly rate (and not based on the property types as in the above example).
To show the records in groups based on the property type, you can partition them by that field. To see an example, change the statement as follows:
```
USE LambdaPropertiesManagement1;
GO

SELECT props.PropertyType,
       props.MonthlyRate Rate,
       RANK() OVER(PARTITION BY props.PropertyType ORDER BY props.MonthlyRate) Ranking
FROM Listing.Properties props;
GO
```
Press F5 to execute
Click inside the Query Editor and press Ctrl + A

Your SQL statement may include a foreign key. To consider an example, type the following code:

USE LambdaPropertiesManagement1;
GO

SELECT pmts.RegistrationNumber [Regist #],
       pmts.AmountPaid Amount,
       RANK() OVER(ORDER BY pmts.AmountPaid) Ranking
FROM Rentals.Payments pmts;
GO

To execute, press F5
Click inside the Query Editor and press Ctrl + A

If the statement has a foreign key, you can use a join to get more meaning values. As an example, type the following statement:

USE LambdaPropertiesManagement1;
GO

SELECT regs.TenantCode [Account #],
       pmts.AmountPaid Amount,
       RANK() OVER(ORDER BY pmts.AmountPaid) Ranking
FROM Rentals.Payments pmts
INNER JOIN Rentals.Registrations regs
ON pmts.RegistrationNumber = regs.RegistrationNumber;
GO

To execute, press F5

Of course, you can create an expression as complex as you want. To try it, change the statement as follows:

USE LambdaPropertiesManagement1;
GO

SELECT DISTINCT(CONCAT(N'Tenant: ', tens.LastName, N' - ', tens.FirstName, N', Property #',
                props.PropertyNumber, N': ', props.PropertyType)) [Registration Information],
       pmts.AmountPaid [Amount Paid],
       RANK() OVER(ORDER BY pmts.AmountPaid) Ranking
FROM Rentals.Payments pmts
INNER JOIN Rentals.Registrations regs
ON pmts.RegistrationNumber = regs.RegistrationNumber
INNER JOIN Rentals.Tenants tens ON tens.TenantCode = regs.TenantCode
INNER JOIN Listing.Properties props ON regs.PropertyNumber = props.PropertyNumber
ORDER BY Ranking;
GO

Press F5 to execute
Click inside the Query Editor, press Ctrl + A, and press Delete.
Switch to the PowerShell window

Densely Ranking Records

When creating groups of records, probably the most basic piece of information you may want to get is what group comes first, followed by which one, and so on. To let you get this information, Transact-SQL provides a function named DENSE_RANK. Its syntax is:

DENSE_RANK() OVER([partition_by_clause] order_by_clause) RETURNS bigint

Like RANK(), this function takes 0 argument and its OVER() clause requires that the records be arranged.

Practical Learning: Ranking the Records Over

To call the DENSE_RANK() function, type the following code and press Enter after each line:

SELECT PropertyType [Property Type],
       DENSE_RANK() OVER(ORDER BY PropertyType) [Ranking by Type]
FROM Listing.Properties;
GO

To execute, right-click inside the Query Editor and click Execute

To see incrementing ranking, change the statement as follows:

USE LambdaPropertiesManagement1;
GO

SELECT DISTINCT(PropertyType) [Property Type],
       DANSE_RANK() OVER(ORDER BY PropertyType) [Ranking by Type]
FROM Listing.Properties;
GO

As mentioned for the RANK() function, you can use a statement that includes various columns. To see an example, switch to the Query Editor and type the following statement:

USE LambdaPropertiesManagement1;
GO

SELECT props.PropertyType [Property Type],
       props.MonthlyRate Rate,
       DENSE_RANK() OVER(ORDER BY props.MonthlyRate) Ranking
FROM Listing.Properties props;
GO

Press F5 to execute
Compare these results with those of the RANK() function.
Click inside the Query Editor and press Ctrl + A

Type the following code:

USE LambdaPropertiesManagement1;
GO

SELECT pmts.PaymentReason Category,
       pmts.AmountPaid Amount,
       DENSE_RANK() OVER(ORDER BY pmts.AmountPaid) Ranking
FROM Rentals.Payments pmts;
GO

Press F5 to execute. Notice the number of payments (107)

To show only the monthly payments, change the statement as follows:

USE LambdaPropertiesManagement1;
GO

SELECT pmts.PaymentReason Category,
       pmts.AmountPaid Amount,
       DENSE_RANK() OVER(ORDER BY pmts.AmountPaid) Ranking
FROM Rentals.Payments pmts
WHERE pmts.PaymentReason = N'Monthly Payment';
GO

Press F5 to execute

To remove the category and show the registrations for which the payments were made, change the statement as follows:

USE LambdaPropertiesManagement1;
GO

SELECT pmts.RegistrationNumber [Regist #],
       pmts.AmountPaid Amount,
       DENSE_RANK() OVER(ORDER BY pmts.AmountPaid) Ranking
FROM Rentals.Payments pmts
WHERE pmts.PaymentReason = N'Monthly Payment';
GO

Press F5 to execute

To group the payments based on registration numbers, change the statement as follows:

USE LambdaPropertiesManagement1;
GO

SELECT pmts.RegistrationNumber [Regist #],
       pmts.AmountPaid Amount,
       DENSE_RANK() OVER(PARTITION BY pmts.RegistrationNumber ORDER BY pmts.AmountPaid) Ranking
FROM Rentals.Payments pmts
WHERE pmts.PaymentReason = N'Monthly Payment';
GO

Press F5 to execute.
Click inside the Query Editor, press Ctrl + A, and press Delete
Switch to the PowerShell window.
To dense rank the payments by registration, type the following code and press Enter after each line:
```
SELECT DISTINCT(pmts.RegistrationNumber) [Regist #],
       pmts.AmountPaid Amount,
       DENSE_RANK() OVER(PARTITION BY pmts.RegistrationNumber
			ORDER BY pmts.AmountPaid) Ranking
FROM Rentals.Payments pmts
WHERE pmts.PaymentReason = N'Monthly Payment';
GO
```
Notice that sometimes different registrations have the same monthly payment (when the monthly rates of different properties are the same) or sometimes the same registration has different payments (when the monthly rate has increased for the same apartment)

Tiling the Records

So far, we were letting the database engine figure out how many groups would be created based on the repeating values of a certain column. Tiling consists of creating a number of groups of records. This means that you must indicate the number of groups you want. This time, it doesn't matter whether you choose a column that has repeating values. You simply create groups of records the way you want. To assist you with this, Transact-SQL provides the NTILE() function. Its syntax is:

NTILE (integer_expression) OVER( [ <partition_by_clause> ] < order_by_clause > )
	RETURNS bigint

The NTILE() function takes one argument as an integer. That number must be positive. The number should be less than the total number of records. If you specify:

1: All records would be created in one group
A number higher than 1 but less than the total number of records, the records would be divided by that number. For example, if you specify 2, the records would be split in 2 groups. Either way, each group would have one part of the fractions. If you pass a number that is not evenly divisible by the total number of records, the first group would have one record more than the other group(s)
A number equal to or higher than the total number of records, each record would belong to its own group

You must call the NTILE() function followed by a call to OVER(), which must have at least an arangement of records. Additionnally, you can specify a partitioning of records. If you do this, the database engine would create the number of groups based on the value passed to the NTILE() function, then each partition would have its own tiling scheme.

Practical Learning: Ranking the Records Over

To title the records, type the following code and press Enter after each line:

1> SELECT tens.TenantCode [Account #],
2>        tens.FirstName  [First Name],
3>        tens.LastName   [Last Name],
4>        tens.MaritalStatus [Status],
5>        NTILE(20) OVER(ORDER BY tens.TenantCode) Tiles
6> FROM Rentals.Tenants tens;
7> GO

Account #  First Name     Last Name    Status    	Tiles
---------- -------------- ------------ ---------------- ---------
204059     Reyza          Haffaz       NULL               1
292470     James          Thomason     Single             2
292475     James          Thomason     Married            3
293750     Diana          Woodson      NULL               4
293759     Michael        Tiernan      Single             5
295800     David          Weaks        Married            6
295804     Mahty          Shaoul       Married            7
385974     Elise          Provoski     Separated          8
495294     Tracy          Warrens      Divorced           9
524790     Christopher    Shermann     Married           10
524794     Nancy          Shermann     Single            11
624050     Frank          Ulm          Single            12
824850     Grace          Flores       Married           13
824857     Grace          Curryan      Married           14
839405     Phillippe      Anderson     Single            15
927407     Ann            Sanders      Married           16

(16 rows affected)
1>

Switch to the Query Editor.
To create different sets of tiles oddly distributed, type the following code:

SELECT tens.TenantCode     [Account #],
       tens.FirstName      [First Name],
       tens.LastName       [Last Name],
       tens.MaritalStatus  [Status],
       NTILE(6) OVER(ORDER BY tens.TenantCode) Tiles
FROM   Rentals.Tenants tens;
GO

Press F5 to execute

To create an even set of tiles, change the statement as follows:

USE LambdaPropertiesManagement1;
GO

SELECT tens.TenantCode     [Account #],
       tens.FirstName      [First Name],
       tens.LastName       [Last Name],
       tens.MaritalStatus  [Status],
       NTILE(4) OVER(ORDER BY tens.TenantCode) Tiles
FROM   Rentals.Tenants tens;
GO

To execute, right-click inside the Query Editor and click Execute. Notice that each group contains the same number of records

To create partitions inside of groups, change the statement as follows:

USE LambdaPropertiesManagement1;
GO

SELECT tens.TenantCode     [Account #],
       tens.FirstName      [First Name],
	   tens.LastName       [Last Name],
	   tens.MaritalStatus  [Status],
       NTILE(4) OVER(PARTITION BY tens.MaritalStatus ORDER BY tens.TenantCode) Tiles
FROM   Rentals.Tenants tens;
GO

To execute, press F5
Close Microsoft SQL Server
When asked whether you want to save, click No


Previous	Copyright © 2012-2022, FunctionX	Next