Numbering rows on condition

Question

I need at select rows from table, make numbering rows so: first 3 rows numbering as:

1  
2  
3

and after ( from the fourth row ), start numbering again and this second numbering continue to the end of all records, that is result must be like this:

I write this query, but this returns usual numbering, from 1 to last record.

Please, someone tell me, where have I logical wrong in my query? In my opinion, this will returns numbring, as I write above

mysql-> SET @n = 0
mysql-> SET @start_again = 'true'


SELECT 

        CASE 
            WHEN  @n = 3 AND @start_again = 'true' THEN  @n := 1
            ELSE @n := @n + 1
        END,

        CASE 
            WHEN  @n = 3 AND @start_again = 'true' THEN  @start_again := 'false'
        END

FROM mytable

I not obtain error, this query works, but incorrect, this returns usuall numbering from 1 to last record. But I need, from the fourth row, start numbering again — Oto Shavadze, Sep 18 '12 at 11:06
What is the difference here to your other question: http://stackoverflow.com/questions/12459340/custom-row-numbering – which has an answer marked as correct??? — feeela, Sep 18 '12 at 11:08
@feeela, in old question I need this numbering: **1,2,3,1,2,3,1,2,3** .In this question I ask, how make this numbering: **1,2,3,1,2,3,4,5,6,7,....** — Oto Shavadze, Sep 18 '12 at 11:10

score 1 · Accepted Answer · answered Sep 18 '12 at 11:07

1

You're overcomplicating your task.

SET @n = 0
SELECT if((@n := @n + 1) > 3, @n - 3, @n) AS n ...

answered Sep 18 '12 at 11:07

lanzz

42,060
10
89
98

Thanks, but if you can , tell me, what is incorrect in my query? why it works incorrect ? – Oto Shavadze Sep 18 '12 at 11:33
1

Because when `@n` is 2, your first `CASE` sees that it is NOT 3 and increments it to 3, _then_ your second `CASE` sees that it is 3 and resets `@start_again` to false. On the next iteration, `@n` is 3, but `@start_again` is already false; the first `CASE` never sees a situation where `@n` is 3 and `@start_again` is true simultaneously. – lanzz Sep 18 '12 at 11:44

Numbering rows on condition

1 Answers1