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.
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 ?TOP(N)
withoutORDER BY
will not guarantee any order !ORDER BY
, but it used columns I removed from the simplified query, so I removed it. Good tip though, thanks!