根据一列的最小值选择行

2021-09-10 00:00:00 sql tsql sql-server

我需要在 MSSQL DB 中选择 A 列具有不同值且 Order 列具有最小值的行.

I need to select row with distinct value of column A and with minimal value of Order column in MSSQL DB.

每条记录的右侧都有更多列.我知道我应该使用 GROUP BY，但是当我想保留右侧列时它确实会发出警告.

Each record has more columns on the right hand side. I know I should used GROUP BY, but it does throw warnings, when I want to keep the right hand side columns a well.

数据集示例:

A       | Order |  Multiple columns ...   |
--------+-------+-------------------------+
A1      |  3    |  ...                    |
A1      |  7    |  ...                    |
A2      |  2    |  ...                    |
A3      |  2    |  ...                    |
A3      |  8    |  ...                    |

所以我想得到这些结果:

So that I want to get these results:

A       | Order |  Multiple columns ...   |
--------+-------+-------------------------+
A1      |  3    |  ...                    |
A2      |  2    |  ...                    |
A3      |  2    |  ...                    |

我尝试使用并抛出警告的查询是这样的:

The query I tried to use and throws warning is this one:

SELECT A, MIN(Order), Column B, Column C, Column D...
    FROM Table
    GROUP BY A
    ORDER BY A

推荐答案

你也可以使用 top (1) with ties

select top (1) with ties a, * 
from table t
order by row_number() over (partition by a order by [Order])

相关文章