英文:
How to return a column in Select Distinct - but exclude it in the Distinct statement
问题
I have a table with data that looks like this:
Num Name City State
12 Bob NYC NY
13 Bob NYC NY
17 John Miami FL
18 John Miami FL
我尝试在这个表上执行DISTINCT操作,有两个要求:
-- 不包括Num在Distinct结果中
-- 在最终结果中包括Num
目标(无论返回哪个Num,只要返回一个即可):
Num Name City State
13 Bob NYC NY
17 John Miami FL
我知道我可以执行一个不排除Num的Distinct操作来获得如下结果:
从Tbl1中选择不同的*
但如果我想在最终结果中包括Num,应该怎么做?
英文:
I have a table with a data that looksl ike this:
Num Name City State
12 Bob NYC NY
13 Bob NYC NY
17 John Miami FL
18 John Miami FL
I'm trying to do a DISTINCT on this table with 2 caveats:
-- don't include Num in Distinct
-- have the Num in end result
Goal (Irrelevant which Num is return, as long as it is returned)
Num Name City State
13 Bob NYC NY
17 John Miami FL
I know I can do a Distinct without excluding Num in it to get the result like so:
Select Distinct * from Tbl1
But what if I want to include Num in the end result?
答案1
得分: 2
你可以简单地使用group by
与max
或min
,因为数字不重要:
select max(num) as num, Name, City, State
from Tbl1
group by Name, City, State
英文:
You can simply use group by
with max
or min
since which num is not important :
select max(num) as num, Name, City, State
from Tbl1
group by Name, City, State
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论