CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2012
Sam
Sam

Reputation: 1908

SQL: efficiently get the last record

I have a table order which contains order date.

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 am currently do:

SELECT TOP 1 * FROM tblOrder WHERE WarehouseId = 1 ORDER BY OrderDate 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?).

Is there any more efficient way to select the last order record?

Thanks

Upvotes: 0

Views: 62

Answers (1)

ADyson
ADyson

Reputation: 61794

If you're going to it a lot, you could consider setting an index on the OrderDate field. That will speed things up (but be aware it might have an impact on other queries against this table - it's a complicated topic, talk to a DBA!).

Otherwise, your query is fine, unless you're worried about the ordering when there are identical dates, in which case you should decide on a secondary field to order by as well, such as OrderID (which you suggested in the comments).

Upvotes: 1

Related Questions