MySQL: Increment column by field

Question

I am trying to create some sort of list of fruits. I want create a temporary column increment_value that takes the count of how much fruit has been inserted by type.

I have this fruits table:

+----------+------------+
| fruit_id | fruit_name | 
+----------+------------+
| 5        | Apple      | 
| 8        | Banana     | 
| 8        | Banana     | 
| 5        | Apple      | 
| 5        | Apple      | 
+----------+------------+

Desired result:

+----------+------------+-----------------+
| fruit_id | fruit_name | increment_value |
+----------+------------+-----------------+
| 5        | Apple      | 1               |
| 8        | Banana     | 1               |
| 8        | Banana     | 2               |
| 5        | Apple      | 2               |
| 5        | Apple      | 3               |
+----------+------------+-----------------+

I tried with dummy table but failed: DEMO

How do I get the table I want?

Why do you store several equal rows? Consider a count column instead. — jarlh, Dec 09 '19 at 11:12
@jarlh I don't save the data like this, it's the result of a join — Alberto Moro, Dec 09 '19 at 11:15
@AlbertoMoro on the contrary, the version matters *a lot*. Only MySQL 8.0 offers a real solution with [Window functions](https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html) like ROW_NUMBER, RANK or DENSE_RANK. In all previous versions you have to use hacks, like incrementing a variable — Panagiotis Kanavos, Dec 09 '19 at 11:15
So, that's what you are trying to do. And over there is what you have tried. Great. But what is the question? — Mike Nakis, Dec 09 '19 at 11:17
I'm fine with the `row_number` too. More than anything else I need an idea to unlock me from this deadlock. — Alberto Moro, Dec 09 '19 at 11:19
@AlbertoMoro check the window functions then. They don't work in any version before 8.0 though. Nor do CTEs, a feature other databases used for this job before they introduced window functions — Panagiotis Kanavos, Dec 09 '19 at 11:21
Thank you @PanagiotisKanavos with that manual I was able to develop the query I needed. — Alberto Moro, Dec 09 '19 at 13:14

score 3 · Accepted Answer · answered Dec 09 '19 at 11:18

3

You can use row_number():

select f.*,
       row_number() over (partition by fruit_name order by fruit_name) as seqnum
from fruits f;

Note: SQL tables and result sets represent unordered sets, so the ordering is indeterminate. You can adjust the ordering by changing the order by clause.

answered Dec 09 '19 at 11:18

Gordon Linoff

1,242,037
58
646
786

... which obviously only makes sense if there's something to order by – Strawberry Dec 09 '19 at 11:35
Thanks, with this answer I was able to develop the query I needed. If you need it below I put the solution for MySQL 5.7 – Alberto Moro Dec 09 '19 at 13:13

score 0 · Answer 2 · answered Dec 09 '19 at 13:11

Thanks to @Gordon's reply. I taking inspiration from this post. I wrote this for MySQL 5.7.

SELECT  
    fruits.*,
    @row_num :=IF(@prev_value= fruit_id, @row_num+1 , 1)AS RowNumber,
    @prev_value := fruit_id
FROM fruits
CROSS JOIN (SELECT @row_num :=1,  @prev_value :=0) vars
ORDER BY fruit_id

Thank you all for your help!

score -1 · Answer 3 · answered Dec 09 '19 at 11:25

-1

you can simply do a group by query like:

SELECT fruit_id, fruit_name, SUM(fruit_id) as increment_value
FROM fruits
GROUP BY fruit_id;

answered Dec 09 '19 at 11:25

Mike Araya

155
1
11

Thanks for the help but this query is not what I was looking for – Alberto Moro Dec 09 '19 at 13:19

MySQL: Increment column by field

3 Answers3