SQLTeam.com | Weblogs | Forums

Ordering based on a given id


#1

Hi there I'm working on a sort of survey where there is a table with categories and a table with questions in the categories, so when you combine them you get something like

Catname Catorder Question Questionorder
Cat1 1 Q1 1
Cat1 1 Q2 2
Cat2 2 Q3 1
Cat2 2 Q4 2
etc.

So, whenever someone is answering for example Q3, I would like to click a button and go to the next unanswered question.
This is what I have so far, however, it is not ideal, because if you are at Q3 and Q1 is not answererd yet, it will go to Q1. However, it should go to Q4
So instead of going to the next unaswered question, it will show the first unanswered question:

SELECT
dt.QuestionID,
cat.CategoryID
FROM
UserSurveys ct
INNER JOIN
Dossier_Details dt
ON
ct.Dossierguid = dt.Dossierguid
Inner join
tbl_Questions vr
ON
vr.QuestionId = dt.QuestionId
inner join
SurveyQuestions ti
on
vr.CategoryId = ti.CategoryId
WHERE
(ct.UserID=@UserID)
And
(dt.Status='Unanswered')
Order by
ti.Order asc, vr.Order asc


#2

So for example when the list of unanswered question returned is:

QuesionID CategoryID
22 3
21 3
24 2
26 2

And the inputparamater is '24', it should give back the record behind this id (so the last record (26 2)) instead of the first.


#3

So it should go to the next question after the MAX answered question, or if none (answered) the FIRST question?

Or do you know the last-viewed-question? Then you could go to next-after-that.

(What happens if I don't want to answer Q3, how do I get to Q4?)


#4

Well the script will run when you click on "Save and go to next question".
So I can add the current question ID as an input parameter.
So the input is: ID 24, the output should be ID 26 (since that is the ID that comes after 24).
However, right now it gives ID 22 (since that is the first unanswered question)