在SQL SERVER中仅选择具有max（id）的行

I have a table A :

我有一张桌子A：

ID     | ProductCatId | ProductCode | Price
1      |       1      |  PROD0001   | 2
2      |       2      |  PROD0005   | 2
3      |       2      |  PROD0005   | 2
4      |       3      |  PROD0008   | 2
5      |       5      |  PROD0009   | 2
6      |       7      |  PROD0012   | 2

I want to select ID,ProductCatId,ProductCode,Price with condition : "if ProductCatId exists same value ,so get ProductCatId with max(ID)", like :

我想选择ID，ProductCatId，ProductCode，Price with condition：“如果ProductCatId存在相同的值，那么得到带有max（ID）的ProductCatId”，如：

ID     | ProductCatId | ProductCode | Price
1      |       1      |  PROD0001   | 2
3      |       2      |  PROD0005   | 2
4      |       3      |  PROD0008   | 2
5      |       5      |  PROD0009   | 2
6      |       7      |  PROD0012   | 2

4 个解决方案

#1

Go for window function and row_number()

转到窗口函数和row_number（）

select ID , ProductCatId , ProductCode , Price
  from (
        select ID , ProductCatId , ProductCode , Price, row_number() over (partition by ProductCatId order by ID desc) as rn
         from myTable
        ) as t
  where t.rn = 1

#2

select 
top 1 with ties
ID,ProductCatId,ProductCode,Price
from
table
order by
row_number() over (partition by productcatid order by id desc)

#3

may use row_number():

可以使用row_number（）：

select t.*
from (select t.*,
             row_number() over (partition by ProductCatId order by ID desc) as seqnum
      from @Table t
     ) t
where seqnum = 1
order by ID;

#4

You can try this,

你可以试试这个，

Select Max(ID),ProductCatId,ProductCode,price
From TableName
Group By ProductCatId,ProductCode,price

#1