Question 291254 in Mysql Question posted in
The official MySQL documentation can be found here.

Incremented virtual in a select with a group constraint with MySQL

I would like to select datas from an alone table with two columns, for a value in the first column there may be only one or multiples different values for the second columnn, but one value in the second can be associated to only one in the first. Both are not unique individually and even concatenated. Example :

col_1    col_2    
=====    =====    
a        x       
a        y        
b        z        
c        w        
b        z        
c        w

Let’s say the col_1 is a group, col_2 a group chapter, and I need to associate a number to each of these chapters relative to their group; needs this number to be deterministic so rows can move and number kept during the lifecycle of the table. The result of the query should be then :

col_1    col_2    generated_col
=====    =====    =============
a        y        1
a        x        2
b        z        1
c        w        1

col_1 + col_2 become unique in the result set, so each new value of col_2 for the same col_1 value gets a +1 to his generated_col.

Is there a way to do this directly in a select query ?

Important note : the MySQL server is on version 5.5

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    This working as expected :

    SELECT DISTINCT col1, col2, (
        SELECT COUNT(DISTINCT col2) 
        FROM yourTable t2 
        WHERE t2.numcmd = t1.numcmd 
        AND t2.col2 <= t1.col2
    ) generated_col
FROM yourTable t1

    Largely inspired by Tim's answer


  2. 0

    On MySQL 8+, you may use the ROW_NUMBER window function:

    SELECT col_1, col_2, col_3,
       ROW_NUMBER() OVER (PARTITION BY col_1 ORDER BY col_3) generated_col
FROM yourTable
ORDER BY col_1, col_3;

    A workaround, for MySQL 5.7 and earlier:

    SELECT col_1, col_2, col_3,
       (SELECT COUNT(*) FROM yourTable t2
        WHERE t2.col_1 = t1.col_1 AND t2.col_3 <= t1.col_3) generated_col
FROM yourTable t1
ORDER BY col_1, col_3;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search