how to manipulate record's ID order?

Question

I'm trying to select a group of list and get results in re-ordered number fashion.

e.g.

ID name

jack
jack
paul
bob
bob
paul
bob

Say I select name='bob' thus got its ID numbers 4,5,7

Now I want the results to be

in this order 1, 2, 3 ...

instead of 4, 5, 7 ...

because it's bob's first second and third, etc...

What's the easiest way to accomplish this?

This looks like you're wanting to discard the existing ID, and assign another number. If that's the case, what you probably need is [ranking](https://stackoverflow.com/questions/3333665/rank-function-in-mysql). — Erayd, Jul 03 '18 at 04:03
@Erayd could you please elaborate? I'm curious as to why something looks simple as this is not so easy to do in mysql. Like you said, it's basically assigning the selected rows with incremented number starting from 1. — dpark123, Jul 03 '18 at 04:22
Which aspect confuses you? Note I'm drawing your attention to the accepted answer in that thread, not the question. — Erayd, Jul 03 '18 at 04:24
Possible duplicate of [Generate serial number in mysql query](https://stackoverflow.com/questions/11094466/generate-serial-number-in-mysql-query) — Govind Samrow, Jul 03 '18 at 07:01

score 0 · Answer 1 · answered Jul 03 '18 at 05:09

Here mysql variables can do the work. Declare & initialize a variable @a = 0, assign an incremented value and select in query. The query should look like:

SET @a = 0;
SELECT @a := @a + 1 AS id, name FROM table_name WHERE name = 'bob';
SET @a = 0;

In the end, again set value of @a = 0.

score 0 · Answer 2 · answered Jul 03 '18 at 06:44

If you are using Mysql 8 then you could use window function rank() and common table expression

with ordered_data as (
  select *, 
  rank() over (partition by name order by id asc ) rnk
  from your_table
  order by rnk
)
select * from ordered_data where name = 'bob';

Or if you are using older release then you could use a correlated query to get the rank

select a.id,a.name,
    (select count(*) 
    from your_table 
     where name= a.name 
     and id <= a.id) rnk
from your_table a
where a.name = 'bob'
order by a.id;

Demo

how to manipulate record's ID order?

ID name

2 Answers2