TOP 1 for Several Record Pairs 1

[grnzbra]

I have a matching table for manufacturers in two different cities. Several manufacturers exist in both cities. One of the manufacturers appears twice in the second city. The result is that in the matching table, that manufacturer's key for the first city appears twice for the second city. The result is:

Manufacturer | OldCityMfgKey | NewCityMfgKey
Westinghouse | 1234567891234 | 4567891234567
Westinghouse | 1234567891234 | 7891234567891

At some point after this, I am going to need to select one of these based on the old key. This would be easy enough to do by using SELECT TOP 1 if I were using a cursor. However, I will need to do it as part of a straight SELECT statement for an entire record set. Is there any way to create a SELECT statement which would select the TOP 1 for any pair of records it encounters in the SELECT statement?

 

[DaveInIowa]

Would it be as simple as this?

;WITH t AS (
SELECT Manufacturer, OldCityMfgKey, NewCityMfgKey,
       ROW_NUMBER() OVER (PARTITION BY Manufacturer, OldCityMfgKey ORDER BY NewCityMfgKey) RowNo
  FROM YourTable
)

SELECT *
  FROM t
 WHERE RowNo = 1

 

[grnzbra]

Thank you. That worked very nicely.