CODEWITHSUNDEEP
sqlpostgresql
Akhmad Ali
Akhmad Ali

Reputation: 125

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

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

Upvotes: 0

Views: 228

Answers (1)

Charlieface
Charlieface

Reputation: 72050

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:

  1. If there is no chance that the two halves of the query can overlap, the UNION ALL will be more efficient than UNION
  2. 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.
  3. 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.
  4. The very first TempTable seems pointless, you could merge it all directly inside your CTE.

Upvotes: 1

Related Questions