SQL Server - returning multiple distinct values

Question

I'm creating a quiz, and I'm storing responses from players in a SQL Server database.

Table: tblResponses

Columns:

    rId (indexed primary key)
    rQuestion (links to tblQuestions)
    rResponse (the player's response, links to tblAnswers)
    rTimeLeft (timestamp of when answer was added)
    rPlayerId (uniqueidentifier, linked to tblPlayers)

Normal operation is fine (reading/writing) but I'm crafting a reporting system and have come a cropper thanks to one feature:

Players can revisit a question and change their answer, which creates a new row in the 'responses' table.

I need to select only the most recent answer in the reports (in this case based on a specific question ID), so if one player has changed their answer at some point I only want to return one record rather than two.

The answer's probably blindingly obvious but I've been looking at it for so long now, obvious is beyond my grasp.

Can anyone help?

Lamak · Accepted Answer · 2012-02-29 12:54:14Z

4

There are many ways to do that. This is an example of one using analytical functions:

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY rQuestion, rPlayerId ORDER BY rId DESC) Corr
      FROM tblResponses) A
WHERE Corr = 1

Disclaimer: This answer works if you have an incremental id on the table tblResponses.

edited Feb 29, 2012 at 12:54

answered Feb 29, 2012 at 12:45

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MassivePenguin Over a year ago

Thanks for all the answers! This one seems to suit my needs best. I'll have to remember this technique for later...

Wim · Accepted Answer · 2012-02-29 12:46:18Z

0

Try this:

SELECT rId, rQuestion, rResponse, rTimeLeft, rPlayerId
FROM (SELECT rId, rQuestion, rResponse, rTimeLeft, rPlayerId,
    ROW_NUMBER() OVER (PARTITION BY rPlayerId, rQuestion ORDER BY rId DESC) as RowNum
    FROM tblResponses
    ) AS T
WHERE T.RowNum = 1

I am assuming here that the rID increases sequentially, higher rID means more recent answer to a question.

answered Feb 29, 2012 at 12:46

Wim

1,1261 gold badge9 silver badges10 bronze badges

Collectives™ on Stack Overflow

SQL Server - returning multiple distinct values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related