|
ms65g
Constraint Violating Yak Guru
497 Posts |
 Posted - 2010-02-10 : 07:22:26
|
[code]
USE Northwind;
--NEW
---- Simple JOIN and Concatenation
;WITH C1 AS
(SELECT *,
CONVERT(VARCHAR(30), OrderDate, 121) +
CAST(OrderID AS VARCHAR(5)) AS Rank
FROM dbo.Orders),
C2 AS
(SELECT CAST(EmployeeID AS VARCHAR) + MAX(Rank) AS Rank
FROM C1
GROUP BY EmployeeID)
SELECT C1.*
FROM C1
JOIN C2
ON CAST(C1.EmployeeID AS VARCHAR) + C1.Rank = C2.Rank
--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(30), 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(30), 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(30), 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;
--NOT EXISTS Predicate
;WITH C AS
(SELECT *,
CONVERT(VARCHAR(30), 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(30), 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(30), 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);
-- JOIN
;WITH C1 AS
(SELECT *,
CONVERT(VARCHAR(30), OrderDate, 121) +
CAST(OrderID AS VARCHAR(5)) AS Rank
FROM dbo.Orders),
C2 AS
(SELECT EmployeeID, MAX(Rank) AS Rank
FROM C1
GROUP BY EmployeeID)
SELECT C1.*
FROM C1
JOIN C2
ON C1.Rank = C2.Rank
AND C1.EmployeeID = C2.EmployeeID
[/code] |
|
The Most Recent Order For Each Employee