将所有列保留在MIN/MAX查询中，但返回1个结果 [英] Keep all columns in MIN / MAX query, but return 1 result

问题描述

我确定我之前已经做过，但是似乎已经忘记了..

I'm sure I've done this before, but seem to have forgotten how..

我正在尝试过滤记录集，以便仅获取1条记录，例如，如果这是我的名为 TableA 的表:

I'm trying to filter a recordset so that I get just the 1 record, so for example, if this is my table called TableA:

|  ID  |  User |  Type  |   Date   |
------------------------------------
|  1   | Matt  | Opened | 1/8/2014 |
|  2   | Matt  | Opened | 2/8/2014 |
|  3   | Matt  | Created| 5/8/2014 |
|  4   | John  | Opened | 1/8/2014 |
|  5   | John  | Created| 2/8/2014 |

我想对其进行过滤，以便获得Date的MIN，其中用户为"Matt"，类型为"Opened".

I'd want to filter it so I get the MIN of Date where the User is "Matt" and the Type is "Opened".

结果集需要包含ID字段并仅返回1条记录，因此它看起来像这样:

The result set needs to include the ID field and return just the 1 record, so it would look like this:

|  ID  |  User |  Type  |   Date   |
------------------------------------
|  1   | Matt  | Opened | 1/8/2014 |

在选择ID字段时，我一直在努力超越GROUPBY要求...这似乎忽略了MIN的Date并返回了1条以上的记录.

I'm struggling with getting past the GROUPBY requirement when selecting the ID field... this seems to ignore MIN of Date and return more than 1 record.

推荐答案

使用TOP和ORDER BY:

select top 1 *
from table
where user = "Matt" and type = "Opened"
order by date asc;

编辑:将顺序从desc更改为asc，因为这实现了我所追求的MIN效果.

Edit: changed order by from desc to asc as this achieves the MIN effect I'm after.

这篇关于将所有列保留在MIN/MAX查询中，但返回1个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！