TSQL - latest entry
I have a contract table w开发者_开发知识库ith id and index_id where the max index_id per id represents the latest entry. How could i get the latest contract in tsql?
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
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
精彩评论