如何在SQL Server中分配订单号

Table: A

表：A

p_id | order | date
-----+-------+---------------
1    |  0    |   2014-01-12
1    |  1    |   2015-02-13

I want to assign the order in desc order based on the latest date.

我想根据最新日期以desc顺序分配订单。

For example, if I receive a record with same p_id on 2017-07-14, I would like to assign the order =2

例如，如果我在2017-07-14收到具有相同p_id的记录，我想分配订单= 2

How do I do that?

我怎么做？

Thanks

谢谢

1 个解决方案

You don't need to store order because your order criteria is date

您无需存储订单，因为您的订单标准是日期

SELECT p_id, date FROM A ORDER BY date

If you need a 0, 1, 2, etc then

如果你需要0,1,2等等

SELECT 
    p_id, date, 
    order = ROW_NUMBER() OVER (PARTITION BY p_id ORDER BY date)
FROM A 
ORDER BY date

Otherwise, order could be an identity column to generate a number automatically

否则，订单可以是标识列以自动生成数字