vba 访问选择特定列上的不同
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14348259/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
access select distinct on certain column
提问by DasPete
I have a table with some search results. The search results maybe repeated because each result may be found using a different metric. I want to then query this table select only the distinct results using the ID column. So to summarize I have a table with an ID column but the IDs may be repeated and I want to select only one of each ID with MS Access SQL, how should I go about doing this?
我有一个包含一些搜索结果的表格。搜索结果可能会重复，因为可以使用不同的度量找到每个结果。然后我想查询这个表，只选择使用 ID 列的不同结果。总而言之，我有一个带有 ID 列的表，但这些 ID 可能会重复，而且我只想使用 MS Access SQL 选择每个 ID 中的一个，我应该怎么做？
Ok I have some more info after trying a couple of the suggestions. The Mins, and Maxes won't work because the column they are operating on cannot be shown. I get an error like You tried to execute a query that does not include the specified expression...I now have all my data sorted, here is what it looks like
好的，在尝试了一些建议后，我有更多信息。Mins 和 Maxes 将不起作用，因为它们正在操作的列无法显示。我收到一个错误，就像 You tried to execute a query that does not include the specified expression...我现在已经对所有数据进行了排序，这是它的样子
ID|Description|searchScore
97 test 1
97 test .95
120 ball .94
97 test .8
120 ball .7
so the problem is that since the rows were put into the table using different search criteria I have duplicated rows with different scores. What I want to do is select only one of each IDsorted by the searchScoredescending. Any ideas?
所以问题是，由于使用不同的搜索条件将行放入表中，因此我复制了具有不同分数的行。我想要做的是只选择每个ID按searchScore降序排序的一个。有任何想法吗？
回答by HansUp
SELECT DISTINCT ID
FROM Search_Table;
Based on the last update to your question, the following query seems appropriate.
根据对您的问题的最后更新，以下查询似乎合适。
SELECT ID, [Description], Max(searchScore)
FROM Search_Table
GROUP BY ID, [Description];
However that's nearly the same as Gordon's suggestion from yesterday, so I'm unsure whether this is what you want.
但是，这与 Gordon 昨天的建议几乎相同，所以我不确定这是否是您想要的。
回答by Gordon Linoff
Here is a way where you can get one of the search criteria:
您可以通过以下方式获取其中一个搜索条件：
select id, min(search_criteria)
from t
group by id
This will always return the first one alphabetically. You can also easily get the last one using max().
这将始终按字母顺序返回第一个。您还可以使用 轻松获得最后一个max()。
You could also use:
您还可以使用：
select id, first(search_criteria)
from t
group by id
