CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
keldar
keldar

Reputation: 6242

SELECT Top 1 ID, DISTINCT Field

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.

Upvotes: 5

Views: 8577

Answers (4)

Kaf
Kaf

Reputation: 33809

If you have a complex scenario where Group By cannot use, You could use Row_Number() function with Common Table Expression.

;WITH CTE AS
(
   SELECT ID, City, ROW_NUMBER() OVER (PARTITION BY City ORDER BY Id) rn
   FROM YourTable
)
SELECT Id, City
FROM CTE
WHERE rn = 1

Upvotes: 3

Robert
Robert

Reputation: 25753

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.

Upvotes: 6

Matt
Matt

Reputation: 15071

You need to have your city in a GROUP BY

SELECT MIN(ID), City
FROM Cities
GROUP BY City

Upvotes: 4

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

More general solution is to use row_number in order to get other details of table:

select * from
(select *, row_number() over(partition by City order by ID) as rn from Cities)
where rn = 1

But for this particular table just grouping will do the work:

select City, Min(ID) as ID
from Cities
group by City

Upvotes: 3

Related Questions