ROW_NUMBER SQL function

1. Usage of ROW_Number.

SQL Server Query 1

            
 SELECT TOP 10  

     ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS SequenceNumber,  

     NCHAR(64 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS UnicodeCharacter  

 FROM  

     sys.objects; -- Using sys.objects as a convenient source of rows  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select().Top(10)  

     .Column(new ROW_NUMBER().ORDER_BY(new OrderBy().SetSelectNull()), "SequenceNumber")  

     .Column(new NCHAR(new ColumnArithmatic().Value(64).ADD()  

         .SqlFunction(new ROW_NUMBER().ORDER_BY(new OrderBy().SetSelectNull()))), "UnicodeCharacter")  

     .From("sys.objects")  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS SequenceNumber,
              NCHAR(@pMAIN_2507200205126436410 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS UnicodeCharacter
FROM sys.objects;


            
        

Parameters (If used)

Query Results 1:

2. Usage of ROW_Number to show customer order lag lead.

SQL Server Query 2

            
 WITH CustomerOrderHistory AS (  

                 SELECT  

                     c.CustomerID,  

                     c.CompanyName,  

                     o.OrderID,  

                     o.OrderDate,  

                     ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY o.OrderDate ASC) AS OrderNumberForCustomer  

                 FROM  

                     Customers c  

                 JOIN  

                     Orders o ON c.CustomerID = o.CustomerID  

             ),  

             CustomerOrderLagLead AS (  

                 SELECT  

                     CustomerID,  

                     CompanyName,  

                     OrderID,  

                     OrderDate,  

                     OrderNumberForCustomer,  

                     LAG(OrderDate, 1, NULL) OVER (PARTITION BY CustomerID ORDER BY OrderDate ASC) AS PreviousOrderDate,  

                     LEAD(OrderDate, 1, NULL) OVER (PARTITION BY CustomerID ORDER BY OrderDate ASC) AS NextOrderDate  

                 FROM  

                     CustomerOrderHistory  

             )  

             SELECT  

                 CustomerID,  

                 CompanyName,  

                 OrderID,  

                 OrderDate,  

                 OrderNumberForCustomer,  

                 PreviousOrderDate,  

                 ISNULL(CAST(DATEDIFF(day, PreviousOrderDate, OrderDate) AS VARCHAR), 'First Order') AS DaysSincePreviousOrder,  

                 NextOrderDate,  

                 ISNULL(CAST(DATEDIFF(day, OrderDate, NextOrderDate) AS VARCHAR), 'Last Order') AS DaysUntilNextOrder  

             FROM  

                 CustomerOrderLagLead  

             ORDER BY  

                 CustomerID,  

                 OrderDate;  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .WithCTETable(new Table("CustomerOrderHistory"), new SqlQueryBuilder()  

         .Select()  

         .Columns("c.CustomerID", "c.CompanyName", "o.OrderID", "o.OrderDate")  

         .Column(new ROW_NUMBER().PARTITION_BY(new Column("c.CustomerID")).ORDER_BY(new OrderBy().Set(new Column("o.OrderDate"))), "OrderNumberForCustomer")  

         .From("Customers", "c")  

         .Join(new List<IJoin>()  

         {  

             new Join(SQLJoin.INNERJOIN).TableName(new Table("Orders","o"))  

                 .On(new Column("c.CustomerID"), SQLComparisonOperators.EQUALE_TO, new Column("o.CustomerID"))  

         })  

     )  

     .WithCTETable(new Table("CustomerOrderLagLead"), new SqlQueryBuilder()  

         .Select()  

         .Columns("CustomerID", "CompanyName", "OrderID", "OrderDate", "OrderNumberForCustomer")  

         .Column(new LAG(new Column("OrderDate"), 1).PARTITION_BY(new Column("CustomerID")).ORDER_BY(new OrderBy().Set(new Column("OrderDate"))), "PreviousOrderDate")  

         .Column(new LEAD(new Column("OrderDate"), 1).PARTITION_BY(new Column("CustomerID")).ORDER_BY(new OrderBy().Set(new Column("OrderDate"))), "NextOrderDate")  

         .From("CustomerOrderHistory")  

     )  

     .Select()  

     .Columns("CustomerID", "CompanyName", "OrderID", "OrderDate", "OrderNumberForCustomer", "PreviousOrderDate")  

     .Column(new ISNULL(  

             new CAST(new DATEDIFF(SqlDateInterval.day, new Column("PreviousOrderDate"), new Column("OrderDate")), SqlDataType.VARCHAR), "First Order"), "DaysSincePreviousOrder")  

     .Column("NextOrderDate", "NextOrderDate")  

     .Column(new ISNULL(new CAST(new DATEDIFF(SqlDateInterval.day, new Column("OrderDate"), new Column("NextOrderDate")), SqlDataType.VARCHAR), "Last Order"), "DaysUntilNextOrder")  

     .From("CustomerOrderLagLead")  

     .OrderBy(new OrderBy().Set(new Column("CustomerID")).Set(new Column("OrderDate")))  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
WITH CustomerOrderHistory
AS (SELECT c.CustomerID,
           c.CompanyName,
           o.OrderID,
           o.OrderDate,
           ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY o.OrderDate ASC) AS OrderNumberForCustomer
    FROM Customers AS c
         INNER JOIN
         Orders AS o
         ON c.CustomerID = o.CustomerID),
 CustomerOrderLagLead
AS (SELECT CustomerID,
           CompanyName,
           OrderID,
           OrderDate,
           OrderNumberForCustomer,
           LAG(OrderDate, @pMAIN_2507200205126538790) OVER (PARTITION BY CustomerID ORDER BY OrderDate ASC) AS PreviousOrderDate,
           LEAD(OrderDate, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate ASC) AS NextOrderDate
    FROM CustomerOrderHistory)
SELECT CustomerID,
       CompanyName,
       OrderID,
       OrderDate,
       OrderNumberForCustomer,
       PreviousOrderDate,
       ISNULL(CAST (DATEDIFF(day, PreviousOrderDate, OrderDate) AS VARCHAR), @pMAIN_2507200205126538791) AS DaysSincePreviousOrder,
       NextOrderDate AS NextOrderDate,
       ISNULL(CAST (DATEDIFF(day, OrderDate, NextOrderDate) AS VARCHAR), @pMAIN_2507200205126538792) AS DaysUntilNextOrder
FROM CustomerOrderLagLead
ORDER BY CustomerID ASC, OrderDate ASC;


            
        

Parameters (If used)

Query Results 2: