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.

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

   

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.

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
    
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:
    
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
    
7. Click inside the Query Editor, press Ctrl + A, and press Delete

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.

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
    
   
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

   
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
    
   
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
     
    
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
     
    
19. Click inside the Query Editor, press Ctrl + A, and press Delete.
    Switch to the PowerShell window