--USE Northwind;

--TOP And ORDER BY
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate
FROM dbo.Orders AS O1
WHERE OrderID =
  	(SELECT TOP(1) OrderID
   FROM dbo.Orders AS O2
  WHERE O2.EmployeeID = O1.EmployeeID
  ORDER BY OrderDate DESC, OrderID DESC);

--MAX
;WITH C AS
(SELECT *,
        CONVERT(VARCHAR(300), OrderDate, 121) + 
        CAST(OrderID AS VARCHAR(5)) AS Rank
   FROM dbo.Orders)
   
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate
  FROM C AS C1
 WHERE Rank =
      (SELECT MAX(Rank)
        FROM C AS C2
       WHERE C1.EmployeeID = C2.EmployeeID);

--MAX
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate
  FROM dbo.Orders t1
 WHERE OrderID = 
     (SELECT MAX(OrderID) 
        FROM dbo.Orders t2
	 WHERE t1.EmployeeID=t2.EmployeeID
	   AND t2.OrderDate = 
		     (SELECT MAX(OrderDate) 
			  FROM dbo.Orders t3
			 WHERE t1.EmployeeID=t3.EmployeeID));


 --Ranking Function
 SELECT *
   FROM (SELECT *,
                Rank = ROW_NUMBER ()
                OVER (PARTITION BY EmployeeID
                      ORDER BY OrderDate DESC, OrderID DESC) 
           FROM dbo.Orders) AS D
  WHERE D.Rank = 1;

--CROSS APPLY Table Operator
SELECT *
  FROM (SELECT DISTINCT EmployeeID
          FROM dbo.Orders) c
 CROSS 
 APPLY (SELECT TOP(1) *
          FROM dbo.Orders t
 	   WHERE c.EmployeeID = t.EmployeeID
         ORDER BY OrderDate DESC, OrderID DESC) cc;  
         
--Correlated Scalar Subquery
;WITH C AS
(SELECT *,
        CONVERT(VARCHAR(300), OrderDate, 121) + 
        CAST(OrderID AS VARCHAR(5)) AS Rank
   FROM dbo.Orders)

SELECT *
 FROM C AS t1
WHERE (SELECT COUNT(*)      
         FROM C AS t2
        WHERE t2.EmployeeID = t1.EmployeeID
          AND t2.Rank > t1.Rank) = 0;
          

--Composite NonEqui Self Join
;WITH C AS
(SELECT *,
        CONVERT(VARCHAR(300), OrderDate, 121) + 
        CAST(OrderID AS VARCHAR(5)) AS Rank
   FROM dbo.Orders)

SELECT t1.OrderID, t1.CustomerID, t1.EmployeeID, t1.OrderDate, t1.RequiredDate 
  FROM C AS t1
  JOIN C AS t2
    ON t1.EmployeeID = t2.EmployeeID
   AND t2.Rank >= t1.Rank
 GROUP BY t1.OrderID, t1.CustomerID, t1.EmployeeID, t1.OrderDate, t1.RequiredDate 
HAVING COUNT(*) = 1;

 
--==================================
 --SOME EQUIVALENCE HERE
--================================== 
 --NOT EXISTS Predicate
 ;WITH C AS
(SELECT *,
        CONVERT(VARCHAR(300), OrderDate, 121) + 
        CAST(OrderID AS VARCHAR(5)) AS Rank
   FROM dbo.Orders)
   
 SELECT *
FROM C t1
WHERE NOT EXISTS
     (SELECT *
        FROM C t2
       WHERE t1.EmployeeID = t2.EmployeeID
         AND t2.Rank > t1.Rank);



--TOP and ORDER BY Clauses
;WITH C AS
(SELECT *,
        CONVERT(VARCHAR(300), OrderDate, 121) + 
        CAST(OrderID AS VARCHAR(5)) AS Rank
   FROM dbo.Orders)
   
SELECT *
FROM C t1
WHERE t1.Rank =
      (SELECT TOP(1) Rank
         FROM C t2
        WHERE t1.EmployeeID = t2.EmployeeID
        ORDER BY Rank DESC);

-- >= ALL (subquery) 
;WITH C AS
(SELECT *,
        CONVERT(VARCHAR(300), OrderDate, 121) + 
        CAST(OrderID AS VARCHAR(5)) AS Rank
   FROM dbo.Orders)
   
SELECT *
FROM C t1
WHERE Rank >= ALL 
     (SELECT Rank 
        FROM C t2
       WHERE t1.EmployeeID = t2.EmployeeID);

quote:

---

Originally posted by Kristen

Useful script, thanks

Could it be moved to SCRIPTS forum?

---