使用GROUP BY，在每个具有最低ID的组中选择记录ID

I am creating a file orginization system where you can add content items to multiple folders. I am storing the data in a table that has a structure similar to the following:

我正在创建一个文件组织系统，您可以在其中向多个文件夹添加内容项。我将数据存储在一个具有类似以下结构的表中:

ID       TypeID    ContentID    FolderID
1         101        1001          1
2         101        1001          2
3         102        1002          3
4         103        1002          2
5         103        1002          1
6         104        1001          1
7         105        1005          2

I am trying to select the first record for each unique TypeID and ContentID pair. For the above table, I would want the results to be:

我正在尝试为每个唯一的类型和内容对选择第一个记录。对于上表，我希望结果是:

ID
1
3
4
6
7

ID 1 3 4 6 7

As you can see, the pairs 101 1001 and 103 1002 were each added to two folders, yet I only want the record with the first folder they were added to.

如您所见，这些对101 1001和103 1002分别添加到两个文件夹中，但我只希望将它们添加到的第一个文件夹的记录。

When I try the following query, however, I only get result that have at least two entries with the same TypeID and ContentID:

但是，当我尝试执行以下查询时，我得到的结果是，至少有两个条目具有相同的类型id和ContentID:

select MIN(ID)
from table
group by TypeID, ContentID

results in

结果

ID
1
4

ID 1 4

If I change MIN(ID) to MAX(ID) I get the correct amount of results, yet I get the record with the last folder they were added to and not the first folder:

如果我将MIN(ID)更改为MAX(ID)，我得到的结果是正确的，但我得到的是添加到最后一个文件夹的记录，而不是第一个文件夹:

ID
2
3
5
6
7

ID 2 3 5 6 7

Am I using GROUP BY or the MIN wrong? Is there another way that I can accomplish this task of selecting the first record of each TypeID ContentID pair?

我用组BY还是用MIN错了?是否有其他方法可以完成选择每个TypeID ContentID对的第一个记录的任务?

4 个解决方案

#1

MIN() and MAX() should return the same amount of rows. Changing the function should not change the number of rows returned in the query.

MIN()和MAX()应该返回相同数量的行。更改函数不应更改查询中返回的行数。

Is this query part of a larger query? From looking at the sample data provided, I would assume that this code is only a snippet from a larger action you are trying to do. Do you later try to join TypeID, ContentID or FolderID with the tables the IDs are referencing?
If yes, this error is likely being caused by another part of your query and not this select statement. If you are using joins or multi-level select statements, you can get different amount of results if the reference tables do not contain a record for all the foreign IDs.

这个查询是较大查询的一部分吗?通过查看所提供的示例数据，我假设这段代码只是您正在尝试执行的较大操作的一个片段。之后是否尝试将TypeID、ContentID或FolderID与id引用的表连接?如果是，这个错误很可能是由查询的另一部分引起的，而不是这个select语句。如果您正在使用join或多级select语句，那么如果引用表不包含所有外部id的记录，您可以获得不同数量的结果。

Another suggestion, check to see if any of the values in your records are NULL. Although this should not affect the GROUP BY, I have sometime encountered strange behavior when dealing with NULL values.

另一个建议是，检查记录中的任何值是否为NULL。虽然这不会影响到GROUP BY，但在处理NULL值时，我有时会遇到奇怪的行为。

#2

Use ROW_NUMBER

使用ROW_NUMBER

WITH CTE AS
(SELECT ID,TypeID,ContentID,FolderID,
ROW_NUMBER() OVER (PARTITION BY TypeID,ContentID ORDER BY ID) as rn FROM t
)
SELECT ID FROM CTE WHERE rn=1

#3

Use it with ORDER BY:

请按下列顺序使用:

select *
from table
group by TypeID, ContentID
order by id

SQLFiddle: http://sqlfiddle.com/#!9/024016/12

SQLFiddle:http://sqlfiddle.com/ ! 9/024016/12

#4

Try with first ( id) instead of min(id)

用first (id)代替min(id)

select first(id) from table group by TypeID, ContentID

通过TypeID、ContentID从表组中选择first(id)

It works ?

它的工作原理吗?

#1