Getting the last answer id for the questions

Question

In M.C.Q test, when user come back to the same question again how can I track the last answer given for the question?,

following is the table structure

Method: 1    
temptblUserAnswer
    id   uid   qid   ansid
    1    1     1     5
    2    1     2     6

Should I update the table value for that particular Question?
OR
Should I use following table structure?

Method: 2    
temptblUserAnswer
    id   uid   qid   ansid   timestamp
    1    1     1     5       2011-09-28 11:54:32
    2    1     2     12      2011-09-28 11:58:40
    3    1     1     7       2011-09-28 12:02:50

Here with the help of timestamp I can find the the last answer for any particular question.

Now the question is, which method to follow, and which will be performance oriented?
Or any better suggestion for the same? Should I go with Method:1 and apply the index on uid, ansid column?

Dalex · Accepted Answer · 2011-09-28T07:28:49.500

2

If your Id is autoincrementing, you can get last answer based on Id:

SELECT TOP 1 AnsID
WHERE Uid=@UserId AND Qid=@QuestionID
ORDER BY Id DESC

About performance:you should put non-clustered index on Uid AND Qid AND ID and Include AnsID

You can avoid of using timestamp column in this table.

edited Sep 28 '11 at 07:28

answered Sep 28 '11 at 07:20

Dalex

3,585
19
25

Bogdan Sahlean · Answer 2 · 2011-09-28T12:05:32.100

0

1: For the first method:

To get the last answer id for a given question id, you can use this script:

   --index creation
   CREATE UNIQUE INDEX IUN_temptblUserAnswer_qid_id ON temptblUserAnswer(uid,qid,id DESC);

   --query
    DECLARE @QuestionID INT, @UserID INT;
    SELECT @QuestionID=123, @UserID = 456;

    SELECT TOP 1 ansid
    FROM temptblUserAnswer
    WHERE qid = @QuestionID
    AND uid = @UserID
    ORDER BY id DESC;

In this case, I have assumed that the last answer is given by the last temptblUserAnswer.id. Also I have assumed that temptblUserAnswer.id is IDENTITY(1,1). Problem: if somebody runs an ad-hoc insert on temptblUserAnswer table it's possible to get wrong results.

2: For the second method:

You should use a date/time data type with higher precision (for SQL version<=SQL2005: DATETIME is the only option, for SQL version>SQL2005 you can use DATETIME2(7) data type and SYSDATETIME() function instead of GETDATE()/CURRENT_TIMESTAMP functions). Even so, you could get two or more answers with the same timestamp. In this case, you could use 'temptblUserAnswer.id' column as the second criteria.

   --index creation
   CREATE UNIQUE INDEX IUN_temptblUserAnswer_qid_id ON temptblUserAnswer(uid,qid,timestamp DESC, id DESC);

   --query
    DECLARE @QuestionID INT, @UserID INT;
    SELECT @QuestionID=123, @UserID = 456;

    SELECT TOP 1 ansid
    FROM temptblUserAnswer
    WHERE qid = @QuestionID
    AND uid = @UserID
    ORDER BY timestamp DESC, id DESC;

edited Sep 28 '11 at 12:05

answered Sep 28 '11 at 07:46

Bogdan Sahlean

19,233
3
42
57

If user is getting back to same question again and change the answer, then should I Insert new record or update existing record. Concerning the performance Inserting new record will be fast, but as you have suggest to create Unique Index on column then the updating record would also perform fine.. what`s your view on this? – Sep 28 '11 at 11:53
1

So, you want the last answer for a question and user. I modified my answer. Performance: you should run some tests. That index will help `SELECT ...` query. – Bogdan Sahlean Sep 28 '11 at 12:09
Thanks, What if I want all lastly given answers ansid for all the question based on UserID, what the query would be? – Sep 28 '11 at 12:38
For what method: 1 or 2 ? What is SQL Server version ? – Bogdan Sahlean Sep 28 '11 at 13:02
I`m using SQL Server 2008, and want solution for method: 1 – Sep 28 '11 at 13:11
Then you should create a new question. Somebody (me or somebody else) will give you an answer. – Bogdan Sahlean Sep 28 '11 at 13:37

Getting the last answer id for the questions

2 Answers2