如何获取SQL Server 2008中每个id的最后插入值？

I have to count the unique status from multiple values. Here is my table example

我必须从多个值中计算出独特的状态。这是我的表格示例

Id  Status  OrderId
-------------------
1     1       43
2     2       43
3     1       44

Desired output

It should give the count(status) for Status '1' is 1 and Status '2' is 1. But when using count its giving 2 for status '1'.

它应该给状态'1'的计数(状态)为1,状态'2'为1.但是当使用count时,它给出状态'1'的2。

2 个解决方案

#1

You have to do

你必须做

count(DISTINCT status)

instead of

count(status)

to get

unique status from multiple values.

多个值的唯一状态。

EDIT:

If you want to get (not count) the Status value of the last inserted record for every OrderId, then you can do:

如果您想获得(不计算)每个OrderId的最后插入记录的状态值,那么您可以执行以下操作:

SELECT Status
FROM (
  SELECT Id,  Status,  OrderId,
         ROW_NUMBER() OVER (PARTITION BY OrderId 
                            ORDER BY Id DESC) AS rn
FROM mytable ) t
WHERE t.rn = 1

#2

If you want to get the last status for each order:

如果您想获得每个订单的最后状态:

with cte as(select *, row_number() 
                       over(partition by OrderID order by Id desc) from TableName)
select * from cte where rn = 1

Or:

select * from (select *, row_number() 
                       over(partition by OrderID order by Id desc) from TableName) t
where rn = 1

#1