CODEWITHSUNDEEP
mysqlsql
Adithya Kumar
Adithya Kumar

Reputation: 159

Create rank variable for column in MySQL

I need to create a rank variable, which would basically be a counter. The query is written to fetch UserId, ExamId. For every userid, there might be multiple examid's and I need to create a new column which would fetch the index of exam. For ex: in the below mentioned table

UserId ExamId CounterVariable

2      3      2-1
2      4      2-2
3      5      3-1
3      6      3-2

SQL:

select us.grade as Grade,k.keyword as QuestionCategory,us.userid as UserId,ue.userExamId as ExamId,ue.completed as ExamCompletedDate,us.userSectionId as UserSectionId,qs.questionSectionId as QuestionSectionId,
    coalesce(a13.answerId, a14.answerId, a15.answerId, a16.answerId) as AnswerId,
    coalesce(a13.answer, a14.answer, a15.answer, a16.answer) as Answer,
    coalesce(a13.correct, a14.correct, a15.correct, a16.correct) as Correct,
    coalesce(a13.flagged, a14.flagged, a15.flagged, a16.flagged) as Flagged,
    coalesce(a13.duration, a14.duration, a15.duration, a16.duration) as Duration,
    coalesce(a13.blank, a14.blank, a15.blank, a16.blank) as Blank
  from userSections us
  join questionSections qs on qs.sectionId = us.sectionId
  join questions q on q.questionId = qs.questionId
  join keywords k on q.keywordId = k.keywordId
  JOIN userExams ue ON ue.userExamId = us.userExamId
  left join
    isee_answers_2013.answers a13 on us.userSectionId = a13.userSectionId and qs.questionSectionId = a13.questionSectionId
  left join
    isee_answers_2014.answers a14 on us.userSectionId = a14.userSectionId and qs.questionSectionId = a14.questionSectionId
  left join
    isee_answers_2015.answers a15 on us.userSectionId = a15.userSectionId and qs.questionSectionId = a15.questionSectionId
  left join
    isee_answers_2016.answers a16 on us.userSectionId = a16.userSectionId and qs.questionSectionId = a16.questionSectionId
 WHERE
    us.valid=1 and us.userid=56
    order by us.grade,k.keyword,us.userid,ue.userExamId,us.userSectionId,qs.questionSectionId

Upvotes: 0

Views: 84

Answers (1)

Sameer Mirji
Sameer Mirji

Reputation: 2245

I'm guessing you need to include a rank column for the query you posted.

In which case, use the below:

SELECT rank, CONCAT(UserId, '-', rank) as CounterVariable, Grade, QuestionCategory, UserId, ExamId, ExamCompletedDate, UserSectionId,
       QuestionSectionId, AnswerId, Answer, Correct, Flagged, Duration, Blank
  FROM (SELECT @rn:=CASE WHEN @rn <> us.userid THEN 1 ELSE @rn+1 END AS rank,
               @usr:=us.userid as usr,
               us.grade as Grade, k.keyword as QuestionCategory, us.userid as UserId,
               ue.userExamId as ExamId, ue.completed as ExamCompletedDate, us.userSectionId as UserSectionId,
               qs.questionSectionId as QuestionSectionId,
               coalesce(a13.answerId, a14.answerId, a15.answerId, a16.answerId) as AnswerId,
               coalesce(a13.answer, a14.answer, a15.answer, a16.answer) as Answer,
               coalesce(a13.correct, a14.correct, a15.correct, a16.correct) as Correct,
               coalesce(a13.flagged, a14.flagged, a15.flagged, a16.flagged) as Flagged,
               coalesce(a13.duration, a14.duration, a15.duration, a16.duration) as Duration,
               coalesce(a13.blank, a14.blank, a15.blank, a16.blank) as Blank
          FROM userSections us
          join questionSections qs on qs.sectionId = us.sectionId
          join questions q on q.questionId = qs.questionId
          join keywords k on q.keywordId = k.keywordId
          JOIN userExams ue ON ue.userExamId = us.userExamId
          left join
    isee_answers_2013.answers a13 on us.userSectionId = a13.userSectionId and qs.questionSectionId = a13.questionSectionId
           left join
    isee_answers_2014.answers a14 on us.userSectionId = a14.userSectionId and qs.questionSectionId = a14.questionSectionId
          left join
    isee_answers_2015.answers a15 on us.userSectionId = a15.userSectionId and qs.questionSectionId = a15.questionSectionId
          left join
    isee_answers_2016.answers a16 on us.userSectionId = a16.userSectionId and qs.questionSectionId = a16.questionSectionId
          join (SELECT @rn:=0) t1
          join (SELECT @usr=0) t2
         WHERE us.valid=1 and us.userid=56
         ORDER BY us.grade,k.keyword,us.userid,ue.userExamId,us.userSectionId,qs.questionSectionId
        ) AS tab;

Upvotes: 1

Related Questions