CODEWITHSUNDEEP
mysqlsqlselectincrement
The Georgia
The Georgia

Reputation: 1075

Mysql increment value in one row each time it reads the same value in another row

Lets say i have a table called customer_order:

id|    cust#|    counter 
 1    1        1
 2    2        1 
 3    1        2 
 4    3        1
 5    2        2
 6    1        3

So the idea is, every time a customer (cust#) repeats, you want to increment counter as demonstrated above.I need to do this in the SELECT statement while quering data. I am using Pentaho kettle to query data from a database. How can i achieve this?

Thank you.

Upvotes: 1

Views: 1568

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can do this with pure sql as:

update customer c
    set counter = (select count(*) from customer c2 where c2.cust# = c.cust# and c2.id <= c.id);

The above should be pretty efficient if you have an index on customer(cust#, id).

You can also do this using variables, which might be more practical if you have more than a few tens of thousands of rows.

EDIT:

Buried deep down, I see the I want to do this in a select. Oops. Here is the select version:

select c.*,
       (select count(*) from customer c2 where c2.cust# = c.cust# and c2.id <= c.id) as counter
from customer c

Upvotes: 1

Related Questions