[SOLVED] How to return a column in Select Distinct - but exclude it in the Distinct statement

How to return a column in Select Distinct - but exclude it in the Distinct statement

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?

Solution

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