[Answered ]-How do i count up when there is a unique value but when there is a duplicate value the count remains the same

by

1👍

✅

Tables are unordered sets, so for this to work you need a column that defines the order of the rows so that you can check in that order if the value of the column Question changes or not.
I don’t see such column in your sample data, so I will use SQLite’s rowid.

Create a CTE that will return a column flag which will indicate if a row is the start of a new Question.
Finally use SUM() window function to get the result that you want:

WITH cte AS (
  SELECT Q.Question_id, Q.Question, PMA.part_model_ans, QP.part_total_marks, MA.answer_mark, Q.rowid, 
         Q.Question <> LAG(Q.Question, 1, '') OVER (PARTITION BY Q.Question_id ORDER BY Q.rowid) flag
  FROM QUESTIONS Q
  LEFT JOIN QUESTIONS_PART QP ON QP.question_id = Q.question_id
  LEFT JOIN PART_MODEL_ANSWER PMA ON PMA.part_id = QP.part_id
  LEFT JOIN MODEL_ANSWER MA ON MA.question_id = Q.question_id
)
SELECT Question_id, Question, part_model_ans, part_total_marks, answer_mark, 
       SUM(flag) OVER (PARTITION BY Question_id ORDER BY rowid) number
FROM cte
ORDER BY question_id

See a simplified demo.

👤forpas

0👍

I think you want order by, not partition by:

DENSE_RANK() OVER (ORDER BY QP.question_id) as number
👤Gordon Linoff

0👍

You want to order by Question, that way the window function will differentiate between questions (SQL Fiddle for further reference):

SELECT 
    Question, 
    DENSE_RANK() OVER (ORDER BY Question) AS ranker
FROM your_data;

OUTPUT:

Question          ranker
----------------- ------
"4+4"             1
"8+8"             2
"What's a dog"    3
"What's a dog"    3
"What's a potato" 4
"What's a potato" 4
👤Aleix CC

Leave a comment