1

I have a table order which contains OrderId (WarehouseId and OrderId are composite primary key).

WarehouseId | OrderId | ItemId | OrderDate
-------------------------------------------
          1 |       1 |      1 | 2016-08-01
          1 |       2 |      2 | 2016-08-02
          1 |       3 |      5 | 2016-08-10
          2 |       1 |      1 | 2016-08-05
          3 |       1 |      6 | 2016-08-06

(table is simplified and only shown required fields)

How to efficiently select the last order for particular Warehouse? I currently do:

SELECT TOP 1 * FROM tblOrder WHERE WarehouseId = 1 ORDER BY OrderId DESC

My concern is, when I have a million (or more) orders for particular warehouse, by doing sorting and select the first record, it will be too slow (I think?). From advice I get so far, it is not an expensive operation, therefore it should be fine. Is that right?

Or, is there any more efficient way to select the last order record?

6

Where appropriate, the database engine can do a backwards scan, ie go straight to the end of the index and work backwards so it's nice and efficient. You can see this on your query execution plans under the 'Scan Direction' attribute of scan operators. For your example, with your composite primary key on WarehouseId,OrderId then this will work:

Backwards Scan

As you are doing select * the clustered index already covers the whole query so no additional bookmark lookups are required.

  • @Martin, thanks for the insight! I used this query in my stored procedure to select the last OrderId "SELECT TOP 1 @@LastOrderId = [OrderId] FROM tblOrder WHERE WarehouseId = 1 ORDER BY OrderId DESC". It is good to know that it is not an expensive operation. – Sam Aug 14 '16 at 0:34
1

You should definitely compare execution plans when trying to figure out which statement is the best performer (and still returns the correct results).

Based on the data in you example and the example select you provided, this should also work

SELECT *
FROM WarehouseTable a
WHERE WarehouseID = 1
    AND OrderID = (
        SELECT MAX(OrderID)
        FROM WarehouseTable
        WHERE WarehouseID = a.WarehouseID
        )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.