Home

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:

Ranking Over the Records Ranking Over the Records Ranking Over the Records

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 LearningPractical Learning: Introducing Record Ranking

  1. Start Microsoft SQL Server and connect
  2. Right-click the name of the server and click New Query
  3. Right-click the name of the server and click Start PowerShell
  4. Type SQLCMD and press Enter
  5. 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
    Introducing Record Ranking

Getting the Row Number in Each Group

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 LearningPractical Learning: Showing the Row Number of Each Record in Each Group

  1. 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
  2. To execute, right-click inside the Query Editor and click Execute
     
    Showing the Row Number of Each Rank Showing the Row Number of Each Rank
    Showing the Row Number of Each Rank
  3. 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
    
    
  4. To execute, press F5.
    Notice that, this time, each group has its own incrementing sequence:
     
    Showing the Row Number of Each Rank Showing the Row Number of Each Rank
    Showing the Row Number of Each Rank
  5. 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
  6. To execute, press F5
     
    Showing the Row Number of Each Rank Showing the Row Number of Each Rank
    Showing the Row Number of Each Rank
  7. 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;
Ranking the Records Over

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 LearningPractical Learning: Ranking the Records Over

  1. 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
  2. To execute, right-click inside the Query Editor and click Execute
     
    Ranking the Records Over Ranking the Records Over
    Ranking the Records Over
  3. 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
  4. To execute, right-click inside the Query Editor and click Execute
  5. Click inside the Query Editor, press Ctrl + A, and press Delete.
    Switch to the PowerShell window
  6. 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
    Ranking the Records Over
  7. 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
  8. Press F5 to execute
     
    Ranking the Records Over Ranking the Records Over
    Ranking the Records Over
  9. 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
  10. Press F5 to execute
     
    Ranking the Records Over Ranking the Records Over
    Ranking the Records Over
  11. Click inside the Query Editor and press Ctrl + A
  12. 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
  13. To execute, press F5
  14. Click inside the Query Editor and press Ctrl + A
  15. 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
  16. To execute, press F5
  17. 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
  18. Press F5 to execute
     
    Ranking the Records Over
  19. 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 LearningPractical Learning: Ranking the Records Over

  1. 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
  2. To execute, right-click inside the Query Editor and click Execute
  3. 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
    Ranking the Records Over
  4. 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
  5. Press F5 to execute
     
    Ranking the Records Over Ranking the Records Over
    Ranking the Records Over
  6. Compare these results with those of the RANK() function.
    Click inside the Query Editor and press Ctrl + A
  7. 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
  8. Press F5 to execute. Notice the number of payments (107)
  9. 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
  10. Press F5 to execute
  11. 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
  12. Press F5 to execute
  13. 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
  14. Press F5 to execute.
    Click inside the Query Editor, press Ctrl + A, and press Delete
  15. 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
    Ranking the Records Over

    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
Tiling the Records

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 LearningPractical Learning: Ranking the Records Over

  1. 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>
  2. 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
  3. Press F5 to execute
     
    Tiling the Records
  4. 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
  5. To execute, right-click inside the Query Editor and click Execute. Notice that each group contains the same number of records
     
    Tiling the Records
  6. 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
  7. To execute, press F5
     
    Tiling the Records
  8. Close Microsoft SQL Server
  9. When asked whether you want to save, click No
 
 
   
 

Previous Copyright © 2012-2013 FunctionX Next