The purpose of the `order by` in the subqueries is to ensure you get the right ID back - otherwise you'll get any ID that matches the criteria.  Alternatively you could change the query to use `MIN(ID)` / `MAX(ID)` instead of `TOP 1 ID`; then you can drop the `order by` clause (that may be faster too for older SQL versions... looking at the current version it seems SQL generates the same query plan for both, so the engine's spotted this optimisation interally).

There are risks with this approach though - say you introduce an additional filter, you'd need to include that on both your main query and your sub queries to ensure things match up.

Additionally, you rely on the ID and Date always being in sync... Sometimes even if that should be true, it's not (e.g. maybe someone's imported data from another system, keeping the dates from the original system; but without updating all of the IDs, because they're just IDs and should have no functional meaning)...  Now your assumption's wrong, and you've got a really hard to trace bug for anyone unaware of this trick.

A cleaner approach may be to create an index over your date column (with additional columns in there if they're also used in the query). The additional overhead of an index will generally be pretty minimal; especially if it's only on the date field, so new rows will always be added on the end.  The benefit will be significant when you're doing simple `>=`, `<=` or `between` type operations.  You may even find it performs better, as whilst the ID is typically the clustered index (and so lives in the same file as the table's data), this custom index can be put on seperate storage (i.e. a separate file to the table's data), so may have less contention / better performance.