TSQL Tuesday: DENSE_RANK for item analysis

As part of their assessment and curriculum review, our curriculum team wanted to know the most commonly selected wrong answer for each question on our district assessments.  This data allows them to identify questions that may be unclear or common misconceptions that need to be addressed in the classroom.

The DENSE_RANK function makes this task easy.   I specifically chose DENSE_RANK as I wanted to allow for ties.  So, if 2 incorrect responses have the same number of selections, both will be returned in my query.

I use DENSE_RANK to assign a rank to each wrong answer choice in descending order (highest count first). Rankings are assigned for each question on each district assessment.

,DENSE_RANK() OVER (PARTITION BY Test, [Question Number] ORDER BY [Student Count] DESC) AS ‘MostCommonWrongAnswer’

To return only the most common wrong answer, I restrict results to those answers with a ranking of 1.

WHERE MostCommonWrongAnswer = 1

The query is attached and can also be found in our GitHub repository.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.