SQL 在同一个查询中选择 DISTINCT 和 MAX
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15486131/
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
Select DISTINCT and MAX in the same query
提问by Vaishak Suresh
I have a table with rows/data in the following structure
我有一个包含以下结构的行/数据的表
-----------
| SURVEY_ID |
------------
| 1 |
| 2 |
| 2 |
| 3 |
| 4 |
-----------
I want to get the distinct IDs and the maximum id in the same query. I tried
我想在同一个查询中获取不同的 ID 和最大 ID。我试过
select distinct(survey_id) as survey_id , max(survey_id) as current
from survey_main
group by survey_id
This doesn't seem to return the correct result. What am I missing?
这似乎没有返回正确的结果。我错过了什么？
Edit: Required result
编辑：所需的结果
----------------------
| Distinct| Max |
----------------------
| 1 | 4 |
| 2 | 4 |
| 3 | 4 |
| 4 | 4 |
----------------------
回答by Trinimon
I think Itay Moav-Malimovka's solution is just perfect, however if you really want to have two columns you could use ....
我认为Itay Moav-Malimovka的解决方案非常完美，但是如果你真的想要两列，你可以使用......
select distinct(survey_id) as identifier,
(select max(survey_id) from survey) as "current"
from survey_main;
Cheers!
干杯!
回答by Itay Moav -Malimovka
SELECT DISTINCT *
FROM T
ORDER BY SURVEY_ID DESC
The first result is the MAX, the entire Dataset is the distinct
第一个结果是 MAX，整个 Dataset 是不同的
回答by BellevueBob
If you are after the count of all survey_ids and the max in one query, try this:
如果您在一个查询中对所有survey_ids 和最大值进行计数，请尝试以下操作：
select count(distinct survey_id) as number_of_survey_ids
, max(survey_id) as current
from survey_main
And if you want to add the max value to every row andyour database supports window functions, try this:
如果您想将最大值添加到每一行并且您的数据库支持窗口函数，请尝试以下操作：
select survey_id
, max(survey_id) over () as current
from survey_main
