add a column from a select query with index

Question

How can I add a column from a select query but the value from the new column will be the row count of the select query for example.

select quantity from menu;

and returns like this

+--------+
|quantity|
+--------+
|   50   |
|   32   |
|   23   |
+--------+

but I want somthing like this

+----------+--------+
|new column|quantity|
+----------+--------+
|     1    |   50   |
|     2    |   32   |
|     3    |   23   |
+----------+--------+

the new column should start from 1 and end from row count of the select query statement. Any answer would help Thanks

user_2469308 · Answer

Since you can access the latest version of MySQL, we can simply use the Row_Number() functionality:

SELECT  
  ROW_NUMBER() OVER () AS new_column, 
  quantity 
FROM menu;

user_3368558 · Answer

See: https://stackoverflow.com/a/6055852/3368558

For your example:

select @rownum:=@rownum+1 as rowNum, quantity 
from menu  
CROSS JOIN (SELECT @rownum:=0) AS user_init;

user_1144035 · Answer

You can use:

select row_number() over (order by quantity desc) as col1, quantity
from menu;

This assumes that you want the rows enumerated by quantity in descending order.

user_1262099 · Answer

What you need to do in this case is first to define the variable @inc using SET and you assign the default value of 0.

Then you include @inc as part of your SELECT statement. You can even use AS to nickname the variable expression.

Also as part of the SELECT you take care of incrementing the value in @inc.

The Code will look something like this:

SET @inc :=0;
SELECT
 @inc := @inc + 1 AS a, 
`some_field` 
FROM 
`some_table`;

Hope will help!

add a column from a select query with index

4 Answers