CODEWITHSUNDEEP
mysqlsqlselectcountwindow-functions
user2671169
user2671169

Reputation: 219

MySQL with counters

My query:

SELECT name FROM users

It's possible add a counter for each differente name. For example:

mark  1
john  1
mark  2
louis 1
john  2
Ann   1

Thank you

Upvotes: 1

Views: 38

Answers (1)

GMB
GMB

Reputation: 222592

If you are running MySQL 8.0, you can use row_number() for this:

SELECT name, ROW_NUMBER() OVER(PARTITION BY name ORDER BY id) rn FROM users

This assumes that you have another column, called id, which can be used to sort the records (that would typically be the primary key of your table).

If you don't have such column, you can remove the ORDER BY clause of the window function - but the results of the query are then unstable, meaning that is not guaranteed that a given row will consistently get the same row number over consecutive executions of the same query. Depending on your use case, this might, or might not be what you want.

In earlier versions of MySQL, you can emulate row_number() with a correlated subquery:

select name, 
    1 + (select count(*) from users u1 where u1.name = u.name and u1.id < u.id) rn
from users u

Upvotes: 1

Related Questions