SELECT Top 1 ID, DISTINCT Field

Question

I have a table sample table as follows:

ID | City
--------------
1  | New York
2  | San Francisco
3  | New York
4  | Los Angeles
5  | Atlanta

I would like to select the distinct City AND the TOP ID for each. E.g., conceptually I would like to do the following

SELECT TOP 1 ID, DISTINCT City
FROM Cities

Should give me:

ID | City
--------------
1  | New York
2  | San Francisco
4  | Los Angeles
5  | Atlanta

Because New York appears twice, it's taken the first ID 1 in this instance.

But I get the error:

Column 'Cities.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer 1

Try this way:

SELECT min(ID), City
FROM Cities
Group by City

MIN function is used for choose one of the ID from two New York cities.