You can use the ROW_NUMBER
and PARTITION BY
StateAbbreviation to obtain the first city per state:
WITH d
AS
(
SELECT StateAbbreviation, CityName
, ROW_NUMBER() OVER
(
PARTITION BY StateAbbreviation
ORDER BY CityName
) AS Ordinal
FROM dbo.Cities
)
SELECT d.StateAbbreviation, StateName, CityName
FROM d
JOIN dbo.States AS s
ON s.StateAbbreviation = d.StateAbbreviation
WHERE Ordinal = 1;
You can change the ORDER BY
clause to whatever suits your requirement but the key here is to make sure that you partition by the StateAbbreviation
to get the row number generated per state.