Rownum in mysql with inner join

Question

i need to add rownum column int below mentioned code, please advice:

SELECT TE.ID,
       TE.QUESTION,
       TE.A,
       TE.B,
       TE.C,
       TE.D,
       TE.E,
       TE.QUESTION_TYPE,
       TE.SCORE,
       S.NAME AS Subject,
       TE.CREATE_DAY,
       TE.CORRECT
FROM TEST_TABLE TE
INNER JOIN SUBJECT S ON S.ID = TE.SUBJECT
WHERE TE.ACTIVE =1
  AND S.ACTIVE =1 LIMIT 1,1;

Answer 1

You can try below code

    SELECT (@row_number:=@row_number + 1) AS num,
       TE.ID,
       TE.QUESTION,
       TE.A,
       TE.B,
       TE.C,
       TE.D,
       TE.E,
       TE.QUESTION_TYPE,
       TE.SCORE,
       S.NAME AS Subject,
       TE.CREATE_DAY,
       TE.CORRECT
     FROM TEST_TABLE TE
    INNER JOIN SUBJECT S ON S.ID = TE.SUBJECT,
   (SELECT @row_number:=0) AS t
   WHERE TE.ACTIVE =1
   AND S.ACTIVE =1 LIMIT 1,1;