TSQL - latest entry

Question

I have a contract table with id and index_id where the max index_id per id represents the latest entry. How could i get the latest contract in tsql?

Answer 1

SELECT TOP 1 whatever_fields_you_want_from_contract_record
FROM ContractTable
ORDER BY index_id DESC

Explanation: The TOP 1 part limits the number of records returned by the query and the ORDER BY index_id DESC part ensures that the returned record(s) will be in DESCending (i.e. biggest value first) order of the index_id value.

Notes:
This type of query will work efficiently if there is an index on index_id (or if the table is relatively small).

Also, the query can be expanded to get the latest contract of a particular kind, by adding a WHERE clause. For example WHERE CustomerId = 123

Answer 2

If you want to get the latest index id (contract) for each id then you could use:

SELECT ID, MAX(INDEX_ID) AS Latest_Index
FROM [Table]
GROUP BY ID