Scenario: A User table and an order table are required to obtain the latest order information for a user.
Scenario: A User table and an order table are required to obtain the latest order information for a user.
To meet the above requirements, we can use the following methods, but the efficiency is quite different.
First, create an index in the Order table:
Create unique index idx_eid_odD_oidD ON Orders (EmployeeID, OrderDate DESC, OrderID DESC)
Multiple orderids are used in descending order of order numbers when OrderData is the same. They are secondary attributes.
Method 1:
The Code is as follows:
SELECT EmployeeID, OrderID FROM Orders AS O1
WHERE OrderID = (
Select top (1) OrderID FROM Orders AS O2
WHERE O1.EmployeeID = O2.EmployeeID
Order by OrderDate DESC, OrderID DESC
)
If you want to obtain the first n orders, change = to IN, And then TOP (n.
Whether it is to retrieve one or more entries, even if there is an index and there is a large amount of data, it is also the slowest.
Method 2:
The Code is as follows:
Select o. EmployeeID, O. OrderID FROM (
SELECT EmployeeID, (select top (1) OrderID FROM Orders AS O2 where e. EmployeeID = O2.EmployeeID order by OrderDate DESC, OrderID DESC) AS OrderID
FROM Employees AS E
) AS EO
Inner join Orders AS O
On eo. OrderID = O. OrderID
Method 2 can take only one message, but not multiple information.
This is much faster than method 1 in the case of retrieving one entry, because the user information is much less than the order information.
Method 3:
The Code is as follows:
Select e. EmployeeID, O. OrderID FROM Employees AS E
Cross apply (
Select top (1) * FROM Orders AS O1 where e. EmployeeID = O1.EmployeeID order by O1.OrderDate DESC, O1.OrderID DESC
) AS O
This application is applied to some new features of SQL Server 2005 or later, which is more efficient than method 2.
If you want to obtain multiple entries, you only need to change TOP (n.
For more information about APPLY, see
Method 4:
The Code is as follows:
SELECT O1.EmployeeID, O1.OrderID
FROM Orders O1 JOIN (
SELECT ROW_NUMBER () OVER (partition by EmployeeID order by OrderDate DESC, OrderID DESC) AS RowNumber ,*
FROM Orders AS OT
) AS O2
ON O1.OrderID = O2.OrderID
WHERE O2.RowNumber = 1
This ROW_NUMBER function is also added after SQL Server 2005. This function does not have much to check with method 3, or even better than method 3. However, you must note that it is first partitioned by EmployeeID and then sorted.
We recommend that you use method 3 based on the above methods.
