MySQL stored procedure how to generate a new auto

I have a procedures like this on MySQL

SELECT t.name AS cat_name , f.id AS id_food 
FROM food AS f, categories AS t
WHERE f.quantity > 0 
AND t.id = f.id_cat 
ORDER BY t.name

When I execute it show a table

| cat_name | id_food |
| -------- | -------------|
| Seafood  | 1            |
| Seafood  | 3            |
| Seafood  | 5            |
| Beef     | 2            |
| Beef     | 16           |
| Special  | 7            |

Was wondering is there anything to make this table generate a new auto_increment id field in front of it?

This is what i want it to look like when execute

| new_id  | cat_name | id_food |
|----     |------    | -----   |
| 1       | Seafood  | 1       |
| 2       | Seafood  | 3       |
| 3       | Seafood  | 5       |
| 4       | Beef     | 2       |
| 5       | Beef     | 16      |
| 6       | Special  | 7       |

CodePudding user response：

If your MySQL version support ROW_NUMBER window function you can try this.

SELECT ROW_NUMBER() OVER(ORDER BY t.name)new_id
       t.name AS cat_name ,
       f.id AS id_food 
FROM food AS f 
INNER JOIN categories AS t 
ON t.id = f.id_cat
WHERE f.quantity > 0 
ORDER BY t.name