SQL Server Management Studio – Using Row_Number, Partition, and filtering on Row_number

Let’s say you want to retrieve only the first row of each question type from the question type table. The 3rd SQL statement will give you your results, but let’s explain each of 3 SQL statements.

select RowNumber, question_type 
 from
   (select ROW_NUMBER() OVER (order by question_type) As RowNumber,
        question_type
    from test_questions) as InnerQuery
    order by question_type


 select RowNumber, question_type
 from
    (select ROW_NUMBER() OVER (PARTITION BY question_type order by question_type) As RowNumber,
        question_type
    from test_questions) as InnerQuery
    order by question_type


 select RowNumber, question_type
 from
    (select ROW_NUMBER() OVER (PARTITION BY question_type order by question_type) As RowNumber,
        question_type
    from test_questions) as InnerQuery
    where InnerQuery.RowNumber = 1
    order by question_type

The 1st SQL statement simply retrieves every single row from the questions table, orders it by question type and assigns a ROW_NUMBER from 1 to whatever for each row retrieved.

The 2nd SQL statement uses the PARTITION keyword to restart the count at 1 again when it discovers a new question type.

The 3rd SQL statement uses the RowNumber in the where statement to filter just the first row of each question type.

This sample can be done by simply using a select distinct on question type, but for the purposes of this exercise, there can be a situation where you would want to retrieve just one row per whatever extra filtering you want in addition to the RowNumber. See Figure 1 for the results.

select distinct question_type from test_questions
Figure 1
Please follow and like us: