The Most Recent Order For Each Employee

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

The Most Recent Order For Each Employee

Author

Topic

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]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-10 : 07:44:59

http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-12 : 01:10:50

So you edited your post to have methods that are specified at the link?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources