1

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

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.

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.