How to get top 1 row for each of the state in USA

Question

I have a list of states in table 1 (Statename, StateAbbreviation) I have different city based records having StateAbbreviations in table 2

I want top 1 record per state from table 2

My result should contain one record for each of the states in table 1

Answer 1

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.