SQL Server TOP(1) 具有不同
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20086243/
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
SQL Server TOP(1) with distinct
提问by Darth.Vader
I am trying to extract the first row I get after ordering the result by i_version_id. If I do not use TOP(2), my query works as expected ans returns all results sorted by i_version_id. But when I add the TOP(2)(as shown below), it says that there is a syntax error near distinct. Please let me know what I am doing wrong.
我正在尝试提取按 对结果进行排序后得到的第一行i_version_id。如果我不使用TOP(2)，我的查询将按预期工作，并返回按 i_version_id 排序的所有结果。但是当我添加TOP(2)（如下所示）时，它说distinct. 请让我知道我做错了什么。
SELECT TOP(2)
distinct(i_version_id)
FROM
[PaymentGateway_2006].[dbo].[merchant]
WHERE
dt_updated_datetime > '2013-11-11'
GROUP BY
i_version_id
ORDER BY
i_version_id;
回答by D Stanley
If you're only getting the TOP 1then distinctis irrelevant. It's also irrelevant since grouping by the column will give you distinct values,
如果你只是得到TOP 1然后distinct是无关紧要的。这也无关紧要，因为按列分组会给你不同的值，
However, If you want more than one just remove the parentheses:
但是，如果您想要多个，只需删除括号：
SELECT DISTINCT TOP(2)
i_version_id
FROM
[PaymentGateway_2006].[dbo].[merchant]
WHERE
dt_updated_datetime > '2013-11-11'
GROUP BY
i_version_id
ORDER BY
i_version_id;
回答by Stuart
Would this work?
这行得通吗？
SELECT
*
FROM
(
SELECT i_version_id,
ROW_NUMBER() OVER (PARTITION BY i_version_id ASC) [counter]
FROM
[PaymentGateway_2006].[dbo].[merchant]
WHERE
dt_updated_datetime > '2013-11-11'
GROUP BY
i_version_id
ORDER BY
i_version_id
) a
WHERE [counter] <= 2
This will give a row counter for each record. Using GROUP BY and DISTINCT in your example above is pointless as your GROUP BY is already restricting your records. Putting in the DISTINCT is just going to effect performance.
这将为每条记录提供一个行计数器。在上面的示例中使用 GROUP BY 和 DISTINCT 毫无意义，因为您的 GROUP BY 已经限制了您的记录。放入 DISTINCT 只会影响性能。
As for your error, you can't use TOP and DISTINCT together AFAIK. You could try this if you wanted too:
至于你的错误，你不能同时使用 TOP 和 DISTINCT AFAIK。如果你也想试试这个：
SELECT
TOP 2 i_version_id
FROM
(
SELECT i_version_id
FROM
[PaymentGateway_2006].[dbo].[merchant]
WHERE
dt_updated_datetime > '2013-11-11'
GROUP BY
i_version_id
ORDER BY
i_version_id
) a
(I haven't tested this as I don't have your Db, but I can't see why this wouldn't do what you need).
（我没有测试过这个，因为我没有你的数据库，但我不明白为什么这不能满足你的需要）。
