1

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.

  • 2
    The desired query would return only one row for distinct values of the calculated column "label" how do you define which one to keep ? The row with 21388 , 21655 or 21726 ? – Kin Shah Aug 26 '15 at 18:35
  • Also, a TOP(N) without ORDER BY will not guarantee any order ! – Kin Shah Aug 26 '15 at 18:46
  • I had an ORDER BY, but it used columns I removed from the simplified query, so I removed it. Good tip though, thanks! – QuickDanger Aug 26 '15 at 19:17
  • @Kin In my example, it doesn't matter which one to keep since the "county" relation became an un-needed complication in this query, so I don't care precisely which city/county it refers to as long as it has the correct city name. The question is mostly about how to work around bad schema if I run into this situation in the future. – QuickDanger Aug 26 '15 at 19:20
  • 2
    Why do you need to return CityID at all? – Aaron Bertrand Aug 26 '15 at 19:47
0

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 :

enter image description here (click here to enlarge)

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.