How to add counter id column based on several columns on SELECT query Postgresql

Question

I would like to create a column with counter number based on question_id and matrix_question_id this is my query:

WITH cte_survey AS 
(
    SELECT 
        user_input_id, 
        question_id,
        type,
        matrix_subtype,
        question,
        matrix_question_id,
        matrix_questions,
        COALESCE(value_text, value_free_text, value_date::text, value_number::text, value) AS all_value
    FROM
        (SELECT 
             a.id, a.user_input_id,
             d.id as question_id, d.type,
             d.matrix_subtype,
             d.question, 
             a.value_suggested_row,
             c.id as matrix_question_id,
             c.value as matrix_questions, 
             a.value_suggested,
             b.value,
             a.value_text, 
             a.value_free_text,
             a.value_date,
             a.value_number
         FROM 
             survey_user_input_line a 
         LEFT JOIN 
             survey_label b on b.id = a.value_suggested
         LEFT JOIN 
             survey_label c on c.id = a.value_suggested_row
         LEFT JOIN 
             survey_question d on d.id = a.question_id
         LEFT JOIN 
             survey_user_input e on e.id = a.user_input_id
         WHERE 
             a.survey_id = 6
         ORDER BY 
             question_id, user_input_id,  matrix_question_id, value_suggested_row) AS TempTable
)
SELECT 
    ROW_NUMBER() OVER (ORDER BY question_id) AS id, * 
FROM
    (SELECT *
     FROM cte_survey 
     WHERE type != 'multiple_choice'

     UNION
 
     SELECT 
         user_input_id, question_id, type,
         matrix_subtype, question, 
         matrix_question_id, matrix_questions,
         STRING_AGG(all_value, ',')
     FROM 
         cte_survey
     WHERE 
         type = 'multiple_choice'
     GROUP BY 
         user_input_id, question_id, type, question, matrix_subtype,
         question, matrix_question_id, matrix_questions
     ORDER BY 
         question_id, matrix_question_id, user_input_id) AS result_answer

and this is the result

I would like to create a new column as id_counter represent id counter base on question_id and matrix_question_id and I wish the result like this

DEMSE_RANK() OVER... maybe? By the way, I'd love to know why you are using UNION and not UNION ALL — Charlieface
– Charlieface, Commented Feb 15, 2021 at 2:59
But you are anyway filtering on type = 'multiple_choice' and != so they are never going to be the same? But you haven't given me the data, so I guess you will know better what you need, I just see people often using UNION needlessly — Charlieface
– Charlieface, Commented Feb 15, 2021 at 3:50
by the way I filtering on type because I folow this tutorial: (stackoverflow.com/questions/66130290/…) — Akhmad Ali
– Akhmad Ali, Commented Feb 15, 2021 at 4:02
Thank for your tips, DENSE_RANK() OVER() is work for this problem — Akhmad Ali
– Akhmad Ali, Commented Feb 15, 2021 at 4:05

Charlieface · Accepted Answer · 2021-02-15 04:11:43Z

What you need here is DENSE_RANK, you can place this next to your row_number:

DENSE_RANK() over (ORDER BY question_id) as id_counter

Further points:

If there is no chance that the two halves of the query can overlap, the UNION ALL will be more efficient than UNION
The row_number is non-deterministic and may return different results each time. You should add an extra ordering clause, perhaps question_id, matrix_question_id, user_input_id or some other unique combination.
ORDER BY on the results of a derived table, view or CTE is not guaranteed to work (unless you are using it with LIMIT, or inside a ranking or window function). You should only place on the very outside of your query.
The very first TempTable seems pointless, you could merge it all directly inside your CTE.

Collectives™ on Stack Overflow

How to add counter id column based on several columns on SELECT query Postgresql

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related