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
