sql - MS SQL - 仅选择一行作为 ID

Question

我有一个包含插入/更新/删除操作的变更日志:

change_id | object_id | operation
----------+-----------+----------
1         | 1         | insert   
2         | 2         | insert   
3         | 1         | delete   
4         | 1         | insert   
5         | 3         | insert   
6         | 2         | delete
7         | 4         | insert
8         | 3         | update

我只需为每个 object_id 选择最后一行，并保持结果按 change_id 排序。结果应该如下所示:

change_id | object_id | operation
----------+-----------+----------
4         | 1         | insert
6         | 2         | delete
7         | 4         | insert
8         | 3         | update

我该怎么做？是否可以使用简单的查询而不使用存储过程？

Answer 1

SQL Fiddle :

SELECT c.change_id, c.object_id, c.operation 
FROM 
(
  SELECT MAX(change_id) AS CID
  FROM changelog 
  GROUP BY object_id
) s
INNER JOIN changelog c on c.change_id = s.CID