Mysql - How to number rows by user?

Question

I have a database table with more than one users and a column with auto increment ID.

The problem is when I create a new record where userId = 2, for example, it put the id of this row with the maximum id of the table.

So it's possible in query select * from items where userId = 2 return the lines numbered starting in 1 even if the first user record is 10 for example.

Example:

  ID | ITEM    | USERID
 - 1 | Item 1  | 1
 - 2 | Item 2  | 2
 - 3 | Item 3  | 2
 - 4 | Item 4  | 1
 - 5 | Item 5  | 2

Select userid = 2 i want this:

   ID | ITEM    | USERID |COUNT
 - 2  | Item 2  | 2      |1
 - 3  | Item 3  | 2      |2
 - 5  | Item 5  | 2      |3

Thank you

for example if a user add new item, the id of this item is the total of items +1, right? if we in this table has 10 records this new record is 11. When I do a query by user id i want return that the number of row is 1 and not 11. @cBroe — user3242861, Sep 07 '17 at 11:05
He basically wants the resulting data with incremental column as first column — Keyur Padalia, Sep 07 '17 at 11:10

Sebastian Brosch · Accepted Answer · 2017-09-07T11:10:46.400

3

You can use the following solution, using a variable:

SET @c_num=0;
SELECT *, @c_num:=@c_num+1 AS 'COUNT' 
FROM table_name 
WHERE USERID = 2
ORDER BY id

edited Sep 07 '17 at 11:10

answered Sep 07 '17 at 11:09

Sebastian Brosch

42,106
15
72
87

`ORDER BY id ASC` should also be included! – Raymond Nijland Sep 07 '17 at 11:10

Mysql - How to number rows by user?

1 Answers1