Window MIN SQL function

1. Usage of MIN to Minimum Order Date for Each Customer

SQL Server Query 1

            
 SELECT  

     c.CustomerID,  

     c.CompanyName,  

     o.OrderID,  

     o.OrderDate,  

     MIN(o.OrderDate) OVER (PARTITION BY c.CustomerID) AS FirstOrderDate  

 FROM Customers c  

 JOIN Orders o ON c.CustomerID = o.CustomerID  

 ORDER BY c.CustomerID, o.OrderDate;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

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

     .Column(new MIN(new Column("o.OrderDate")).OVER(new OVER().PARTITION_BY(new Column("c.CustomerID"))), "FirstOrderDate")  

     .From("Customers","c")  

     .Join(new List<IJoin>()   

     {  

         new INNERJOIN().TableName("Orders","o")  

             .On(new Column("c.CustomerID").Equale(new Column("o.CustomerID")))  

     })  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT c.CustomerID,
       c.CompanyName,
       o.OrderID,
       o.OrderDate,
       MIN(o.OrderDate) OVER (PARTITION BY c.CustomerID) AS FirstOrderDate
FROM Customers AS c
     INNER JOIN
     Orders AS o
     ON c.CustomerID = o.CustomerID;


            
        

Parameters (If used)

Query Results 1:

2. Usage of MIN to find Product Price to the Minimum Price within its Category

SQL Server Query 2

            
 SELECT  

     p.ProductName,  

     c.CategoryName,  

     p.UnitPrice,  

     MIN(p.UnitPrice) OVER (PARTITION BY c.CategoryName) AS MinimumCategoryPrice,  

     CASE  

         WHEN p.UnitPrice = MIN(p.UnitPrice) OVER (PARTITION BY c.CategoryName) THEN 'Lowest Price in Category'  

         ELSE ''  

     END AS PriceStatus  

 FROM Products p  

 JOIN Categories c ON p.CategoryID = c.CategoryID  

 ORDER BY c.CategoryName, p.UnitPrice;  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

     .Columns("p.ProductName", "c.CategoryName", "p.UnitPrice")  

     .Column(new MIN(new Column("p.UnitPrice")).OVER(new OVER().PARTITION_BY(new Column("c.CategoryName"))), "MinimumCategoryPrice")  

     .Column(new CASE()  

         .When(new Column("p.UnitPrice").Equale(new MIN(new Column("p.UnitPrice")).OVER(new OVER().PARTITION_BY(new Column("c.CategoryName")))))  

         .Then("Lowest Price in Category").Else(new SPACE(0))  

     , "PriceStatus")  

     .From("Products", "p")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName(new Table("Categories", "c"))  

             .On(new Column("p.CategoryID").Equale(new Column("c.CategoryID")))  

     })  

     .OrderBy(new OrderBy()  

         .SetColumnAscending("c.CategoryName")  

         .SetColumnAscending("p.UnitPrice")  

     )  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT p.ProductName,
       c.CategoryName,
       p.UnitPrice,
       MIN(p.UnitPrice) OVER (PARTITION BY c.CategoryName) AS MinimumCategoryPrice,
       CASE WHEN p.UnitPrice = MIN(p.UnitPrice) OVER (PARTITION BY c.CategoryName) THEN @pMAIN_2507192014395743120 ELSE SPACE(@pMAIN_2507192014395743121) END AS PriceStatus
FROM Products AS p
     INNER JOIN
     Categories AS c
     ON p.CategoryID = c.CategoryID
ORDER BY c.CategoryName ASC, p.UnitPrice ASC;


            
        

Parameters (If used)

Query Results 2:

3. Usage of MIN (using outer apply) to find the Employee Who Handled the Earliest Order for Each Customer

SQL Server Query 3

            
 WITH CustomerFirstOrders AS (  

     SELECT  

         c.CustomerID,  

         MIN(o.OrderDate) AS FirstOrderDate  

     FROM Customers c  

     JOIN Orders o ON c.CustomerID = o.CustomerID  

     GROUP BY c.CustomerID  

 )  

 SELECT  

     cfo.CustomerID,  

     c.CompanyName,  

     cfo.FirstOrderDate,  

     e.EmployeeID,  

     e.FirstName + ' ' + e.LastName AS FirstOrderEmployee  

 FROM CustomerFirstOrders cfo  

 JOIN Customers c ON cfo.CustomerID = c.CustomerID  

 OUTER APPLY (  

     SELECT TOP 1  

         o.EmployeeID,  

         emp.FirstName,  

         emp.LastName  

     FROM Orders o  

     JOIN Employees emp ON o.EmployeeID = emp.EmployeeID  

     WHERE o.CustomerID = cfo.CustomerID  

     ORDER BY o.OrderDate ASC  

 ) AS e  

 ORDER BY cfo.CustomerID;  

            
        

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  

     .WithCTETable(new Table("CustomerFirstOrders"), new SqlQueryBuilder().Select()  

         .Columns("c.CustomerID")  

         .Column(new MIN(new Column("o.OrderDate")), "FirstOrderDate")  

         .From("Customers"," c")  

         .Join(new List<IJoin>()   

         {  

             new INNERJOIN().TableName("Orders","o")  

                 .On(new Column("c.CustomerID").Equale(new Column("o.CustomerID")))  

         })  

         .GroupBy(new GroupBy("c.CustomerID"))  

     )  

     .Select()  

     .Columns("cfo.CustomerID","c.CompanyName","cfo.FirstOrderDate","e.EmployeeID")  

     .Column(new CONCAT(new Column("e.FirstName"), new SPACE(1), new Column("e.LastName")),"FirstOrderEmployee")  

     .From("CustomerFirstOrders","cfo")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName(new Table("Customers","c"))  

             .On(new Column("cfo.CustomerID").Equale(new Column("c.CustomerID")))  

     })  

     .OuterApply(new SqlQueryBuilder().Select().Top(1)  

         .Columns("o.EmployeeID","emp.FirstName","emp.LastName")  

         .From("Orders","o")  

         .Join(new List<IJoin>() {  

             new INNERJOIN().TableName("Employees", "emp")  

                 .On(new Column("o.EmployeeID").Equale(new Column("emp.EmployeeID")))  

         })  

         .Where(new Where(new Column("o.CustomerID").Equale(new Column("cfo.CustomerID"))))  

         .OrderBy(new OrderBy()  

             .SetColumnAscending("o.OrderDate"))  

     , "e")  

     .OrderBy(new OrderBy()  

         .SetColumnAscending("cfo.CustomerID")  

     )  

     .Build();  

            
        

Query build by SqlQueryBuilder 3

            
WITH CustomerFirstOrders
AS (SELECT c.CustomerID,
           MIN(o.OrderDate) AS FirstOrderDate
    FROM Customers AS c
         INNER JOIN
         Orders AS o
         ON c.CustomerID = o.CustomerID
    GROUP BY c.CustomerID)
SELECT cfo.CustomerID,
       c.CompanyName,
       cfo.FirstOrderDate,
       e.EmployeeID,
       CONCAT(e.FirstName, SPACE(@pMAIN_2507192014395838880), e.LastName) AS FirstOrderEmployee
FROM CustomerFirstOrders AS cfo
     INNER JOIN
     Customers AS c
     ON cfo.CustomerID = c.CustomerID OUTER APPLY (SELECT TOP 1 o.EmployeeID,
                                                                emp.FirstName,
                                                                emp.LastName
                                                   FROM Orders AS o
                                                        INNER JOIN
                                                        Employees AS emp
                                                        ON o.EmployeeID = emp.EmployeeID
                                                   WHERE o.CustomerID = cfo.CustomerID
                                                   ORDER BY o.OrderDate ASC) AS e
ORDER BY cfo.CustomerID ASC;


            
        

Parameters (If used)

Query Results 3:

4. Usage of MIN (using cross apply) to find the Employee Who Handled the Earliest Order for Each Customer

SQL Server Query 4

            
 SELECT DISTINCT  

     c.CustomerID,  

     c.CompanyName,  

     FirstOrder.FirstOrderDate,  

     e.EmployeeID,  

     e.FirstName + ' ' + e.LastName AS FirstOrderEmployee  

 FROM Customers c  

 JOIN Orders o ON c.CustomerID = o.CustomerID  

 CROSS APPLY (  

     SELECT TOP 1  

         o2.OrderDate AS FirstOrderDate,  

         o2.EmployeeID  

     FROM Orders o2  

     WHERE o2.CustomerID = c.CustomerID  

     ORDER BY o2.OrderDate ASC  

 ) AS FirstOrder  

 JOIN Employees e ON FirstOrder.EmployeeID = e.EmployeeID  

 WHERE o.OrderDate = FirstOrder.FirstOrderDate  

 ORDER BY c.CustomerID;  

            
        

Create SQL query with SqlQueryBuilder 4

            
 var (sql4, parameters4) = new SqlQueryBuilder()  

     .Select().Distinct()  

     .Columns("c.CustomerID", "c.CompanyName", "FirstOrder.FirstOrderDate", "e.EmployeeID")  

     .Column(new CONCAT(new Column("e.FirstName"), new SPACE(1), new Column("e.LastName")), "FirstOrderEmployee")  

     .From("Customers","c")  

     .Join(new List<IJoin>()   

     {  

         new INNERJOIN().TableName("Orders","o")  

             .On(new Column("c.CustomerID").Equale(new Column("o.CustomerID")))  

     })  

     .CrossApply(new SqlQueryBuilder().Select().Top(1)  

         .Column("o2.OrderDate", "FirstOrderDate")  

         .Column("o2.EmployeeID", "EmployeeID")  

         .From("Orders","o2")  

         .Where(new Where(new Column("o2.CustomerID").Equale(new Column("c.CustomerID"))))  

         .OrderBy(new OrderBy().SetColumnAscending("o2.OrderDate"))  

     , "FirstOrder")  

     .Join(new List<IJoin>()   

     {  

         new INNERJOIN().TableName("Employees","e")  

             .On(new Column("FirstOrder.EmployeeID").Equale(new Column("e.EmployeeID")))  

     })  

     .Where(new Where(new Column("o.OrderDate").Equale(new Column("FirstOrder.FirstOrderDate"))))  

     .OrderBy(new OrderBy().SetColumnAscending("c.CustomerID"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 4

            
SELECT DISTINCT c.CustomerID,
                c.CompanyName,
                FirstOrder.FirstOrderDate,
                e.EmployeeID,
                CONCAT(e.FirstName, SPACE(@pMAIN_2507192014396112800), e.LastName) AS FirstOrderEmployee
FROM Customers AS c
     INNER JOIN
     Orders AS o
     ON c.CustomerID = o.CustomerID CROSS APPLY (SELECT TOP 1 o2.OrderDate AS FirstOrderDate,
                                                              o2.EmployeeID AS EmployeeID
                                                 FROM Orders AS o2
                                                 WHERE o2.CustomerID = c.CustomerID
                                                 ORDER BY o2.OrderDate ASC) AS FirstOrder
     INNER JOIN
     Employees AS e
     ON FirstOrder.EmployeeID = e.EmployeeID
WHERE o.OrderDate = FirstOrder.FirstOrderDate
ORDER BY c.CustomerID ASC;


            
        

Parameters (If used)

Query Results 4:

5. Usage of MIN to identifying the Cheapest Product in Each Order

SQL Server Query 5

            
 SELECT  

     od.OrderID,  

     p.ProductName,  

     od.UnitPrice,  

     MIN(od.UnitPrice) OVER (PARTITION BY od.OrderID) AS CheapestProductPriceInOrder,  

     CASE  

         WHEN od.UnitPrice = MIN(od.UnitPrice) OVER (PARTITION BY od.OrderID) THEN 'Cheapest in Order'  

         ELSE ''  

     END AS PriceStatusInOrder  

 FROM [Order Details] od  

 JOIN Products p ON od.ProductID = p.ProductID  

 ORDER BY od.OrderID, od.UnitPrice;  

            
        

Create SQL query with SqlQueryBuilder 5

            
 var (sql5, parameters5) = new SqlQueryBuilder()  

     .Select().Distinct()  

     .Columns("od.OrderID","p.ProductName","od.UnitPrice")  

     .Column(new MIN(new Column("od.UnitPrice")).OVER(new OVER().PARTITION_BY(new Column("od.OrderID"))), "CheapestProductPriceInOrder")  

     .Column(new CASE()  

         .When(new Column("od.UnitPrice").Equale(new MIN(new Column("od.UnitPrice")).OVER(new OVER().PARTITION_BY(new Column("od.OrderID")))))  

         .Then("Cheapest in Order")  

         .Else(new SPACE(0))  

     , "PriceStatusInOrder")  

     .From("[Order Details]","od")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName("Products","p")  

             .On(new Column("od.ProductID").Equale(new Column("p.ProductID")))  

     })  

     .OrderBy(new OrderBy()  

         .SetColumnAscending("od.OrderID")  

         .SetColumnAscending("od.UnitPrice"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 5

            
SELECT DISTINCT od.OrderID,
                p.ProductName,
                od.UnitPrice,
                MIN(od.UnitPrice) OVER (PARTITION BY od.OrderID) AS CheapestProductPriceInOrder,
                CASE WHEN od.UnitPrice = MIN(od.UnitPrice) OVER (PARTITION BY od.OrderID) THEN @pMAIN_2507192014396449070 ELSE SPACE(@pMAIN_2507192014396449071) END AS PriceStatusInOrder
FROM [Order Details] AS od
     INNER JOIN
     Products AS p
     ON od.ProductID = p.ProductID
ORDER BY od.OrderID ASC, od.UnitPrice ASC;


            
        

Parameters (If used)

Query Results 5: