按记录分组然后从每个组中获取最后一个升序排序记录？

I have a table:

我有一张桌子:

| P_id | Month |  Year  |  Name  |
   624     09     2009    Test1
   600     10     2010    Test2
   624     10     2010    Test6
   600     09     2011    Test7

I want to order my table by P_id descending, Year ascending, then Month ascending

我想通过P_id降序,年升序,然后月升序来命令我的表
Based on the order I want the last record for each P_id.

根据订单,我想要每个P_id的最后一条记录。

After step 1 is run:

运行第1步后:

| P_id | Month |  Year  |  Name  |
  624     09      2009    Test1
  624     10      2010    Test6
  600     10      2010    Test2
  600     09      2011    Test7

Desired Output:

| P_id | Month |  Year  |  Name  |
   624     10     2010    Test6
   600     09     2011    Test7

1 个解决方案

#1

You could use row_number:

你可以使用row_number:

select  *
from    (
        select  row_number() over (
                  partition by p_id
                  order by year desc, month desc) as rn
        ,       *
        from    YourTable
        ) as SubQueryAlias
where   rn = 1

The subquery is required because you can't use row_number directly in the where clause.

子查询是必需的,因为您不能直接在where子句中使用row_number。

#1