如何在Select Distinct中返回列 – 但在Distinct语句中排除它。

huangapple go评论60阅读模式
英文:

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 bymaxmin,因为数字不重要:

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

huangapple
  • 本文由 发表于 2023年2月24日 04:33:08
  • 转载请务必保留本文链接:https://go.coder-hub.com/75550029.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定