“MYSQL: How do i put numbering? ”

Question

I am trying to find a MySQL query that will put numbering on each DISTINCT values.

id         name
-----      ------
1          Mark
2          Mike
3          Paul
4          Mike
5          Mike
6          John
7          Mark

expected result. something like this

count       name
-----      ------
1          Mark
2          Mark
1          Mike
2          Mike
3          Mike
1          John
1          Paul

not a query generator – Lightness Races in Orbit Oct 16 '19 at 23:28 — Lightness Races in Orbit, Oct 16 '19 at 23:28

GMB · Accepted Answer · 2019-10-16T23:25:54.743

0

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

select
    row_number() over(partition by name order by id) `count`,
    name
from mytable
order by name, `count`

The query relies on column id to order the records because it seems to be unique (without a column whose value is unique within the groups, the ordering of the records having the same name is not predictable).

On earlier versions, row_number() can be emulated with variables:

select 
    case when @name = name then @cnt := @cnt + 1 else @cnt := 1 end `count`,
    @name := name
from 
    (select name from mytable order by name, id) t
    cross join (select @cnt := 1, @name := null) x
order by name, `count`

Demo on DB Fiddle - both queries return:

| ----- | -------------- |
| 1     | John           |
| 1     | Mark           |
| 2     | Mark           |
| 1     | Mike           |
| 2     | Mike           |
| 3     | Mike           |
| 1     | Paul           |

edited Oct 16 '19 at 23:25

answered Oct 16 '19 at 23:08

GMB

216,147
25
84
135

Isn't this actually a rank within the partition? – Barmar Oct 16 '19 at 23:10
@Barmar: well, at long as there is a unique column for ordering (here: `id`), this doesn't make a difference. But my understanding is that, if there wasn't such a column, then we really would need `row_number()` over `rank()`; else, there would be a risk that two records of the same partition would be assigned the same rank, which is apparently not what the OP wants. – GMB Oct 16 '19 at 23:14
True, your solution allows for duplicate IDs, but it means that the ordering will be unpredictable. That's immaterial if you're only returning `name`, but could make a diffference if other columns are added. – Barmar Oct 16 '19 at 23:16
@Barmar: `rank()` will not help for this. To get the numbering in a predictable order (if that matters), then we need a column (or a combination of columns, or an expression) whose value is unique within each group. – GMB Oct 16 '19 at 23:27
Assuming `id` is the primary key, it will necessarily be unique within the group. – Barmar Oct 16 '19 at 23:34

“MYSQL: How do i put numbering? ”

1 Answers1