当列数很多时，从每个组中选择第一行

Suppose I have a table as follows:

假设我有一个表如下：

Name Part  Address City   ... Stuff 
Bob   X1     Y1      Z1   ... Stuff1
Bob   X2     Y1      Z1   ... Stuff1       
Bob   X3     Y1      Z1   ... Stuff1
Susan V1     Y2      Z2   ... Stuff2
Susan V2     Y2      Z2   ... Stuff2
....

Here, Stuff is many columns. Notice that Address, City, Stuff doesn't change.

在这里，Stuff是很多专栏。请注意，地址，城市，东西不会改变。

So I just want to return the first row. I know I need to do something like

所以我只想返回第一行。我知道我需要做类似的事情

SELECT * FROM myTable
GROUP_BY (NAME)

but I'm not sure how to select the first row only after the group by? I saw other posts but they all were selecting based upon a min.. since my columns aren't numeric, I'm not sure how they would apply?

但是我不知道如何在小组之后选择第一行呢？我看到其他帖子，但他们都是根据最小值选择...因为我的列不是数字，我不确定它们将如何应用？

3 个解决方案

#1

Use ROW_NUMBER instead.

请改用ROW_NUMBER。

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

返回结果集的分区中的行的序号，从1开始，每个分区的第一行。

SELECT *
FROM (
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Part)
    FROM tbl
) AS t
WHERE rn = 1

You can replace the ORDER BY column depending on your definition of first.

您可以根据您的第一个定义替换ORDER BY列。

#2

try this

尝试这个

 WITH CTE
    AS
    (
    SELECT ROW_NUMBER() OVER(PARTITION BY GroupColumn ORDER BY SomeColumn) as indx
        FROM YourTABLENAME
    )

    select * from CTE where indx=1

#3

-1

you can use top1

你可以使用top1

     SELECT top(1) Name FROM myTable GROUP_BY (NAME),

in groupby, you cannot show column that is not in groupby with out using aggrigrate function

在groupby中，不能使用aggrigrate函数显示不在groupby中的列

so you have option to do like

所以你可以选择做

SELECT top(1) Name,Address, City FROM myTable GROUP_BY NAME,Address, City

要么

SELECT top(1) Name,stuff((select ','+Address+city from myTable mt where mt.Name=myTable.Name for xmlpath('')),1,1,'') FROM myTable GROUP_BY NAME

#1