Query to remove duplicate calculated values

Question

I have a client running SQL Server 2008 R2 SP1, and I can't seem to write a query that has distinct calculated values. I could change the schema to store the value being calculated, but I was curious if there was a way to do this from within a query without modifying schema (for scenarios where client schema modification is not possible)?

City Table:

+-------+----------+--------------------------+----------+
|  id   |   name   |       displayName        | countyID |
+-------+----------+--------------------------+----------+
| ...   | ...      | ...                      | ...      |
| 833   | Portland | Portland, Ashley, AR     | 3323     |
| 21388 | Portland | Portland, Clackamas, OR  | 5439     |
| 21655 | Portland | Portland, Multnomah, OR  | 5462     |
| 21726 | Portland | Portland, Washington, OR | 5470     |
+-------+----------+--------------------------+----------+

County Table

+------+------------+----------------+---------+
|  id  |    name    |  displayName   | stateID |
+------+------------+----------------+---------+
| ...  | ...        | ...            | ...     |
| 5439 | Clackamas  | Clackamas, OR  | 38      |
| 5462 | Multnomah  | Multnomah, OR  | 38      |
| 5470 | Washington | Washington, OR | 38      |
+------+------------+----------------+---------+

State Table

+-----+-------+---------------+
| id  | State | StateFullName |
+-----+-------+---------------+
| ... | ...   | ...           |
| 38  | OR    | Oregon        |
+-----+-------+---------------+

Query:

   select top 100 City.id as cityID
    ,City.name + ', ' + [State].[State] as label
    ,[State].[State] as stateAbbrev
    ,[State].StateFullName as stateName
from [City]
left join [Country] on (City.countyID = Country.id)
left join [State] on (Country.stateID = [State].id)
where City.name + ', ' + [State].[State] like 'Portland, OR'

Results:

+--------+--------------+-------------+-----------+
| cityID |    label     | stateAbbrev | stateName |
+--------+--------------+-------------+-----------+
|  21388 | Portland, OR | OR          | Oregon    |
|  21655 | Portland, OR | OR          | Oregon    |
|  21726 | Portland, OR | OR          | Oregon    |
+--------+--------------+-------------+-----------+

The desired query would return only one row for distinct values of the calculated column "label". As I mentioned earlier, I'm curious if there's a solution that doesn't involve schema modification since that may not always be possible.

Answer 1

Converting my comment as answer (SQLFiddle):

;with cte (cityID, label, rowNum, stateAbbrev, stateName)
as (
    SELECT 
    TOP 100 City.id AS cityID,
    City.name + ', ' + [State].[State]  as label,
    row_number() over (partition by cast(City.name + ', ' + [State].[State] as varchar(max)) order by City.id desc) AS rowNum, -- if you want the highest cityID then use "desc" else remove the "desc"
    [State].[State] AS stateAbbrev,
    [State].StateFullName AS stateName
  FROM [City]
  LEFT JOIN [Country] ON(City.countyID = Country.id)
  LEFT JOIN [State] ON(Country.stateID = [State].id)
  WHERE City.name + ', ' + [State].[State] LIKE 'Por%'
  order by City.id)  -- I have put in order by 
select cityID, label, stateAbbrev, stateName
        from cte
        where rowNum = 1

The above will result :

(click here to enlarge)

Note: As Aaron commented, you should remove CityID if not required in output list.