If you use an index that is sorted the way you would retrieve it, the SELECT statement would not do additional sorting when in retrieval.
When you execute the query
The database system would not do additional sorting, since it can do an index-lookup in that order.
Partial or Filtered Index
SQL Server and SQLite allow to create indexes that contain not only a subset of columns, but also a subset of rows.
Consider a constant growing amount of orders with order_state_id equal to finished (2), and a stable amount of orders with order_state_id equal to started (1).
If your business make use of queries like this:
Partial indexing allows you to limit the index, including only the unfinished orders:
This index will be smaller than an unfiltered index, which saves space and reduces the cost of updating the index.