SQL Function to Replace specific text with Count

Question

I'm looking for a function to get result in below format:

Input:

| name     |
| -------- |
| DFHG     |
| DRTYFGJN |
| FGH      |
| FGH      |
| NBHFGD   |
| OIUKJH   |
| RTFHGJ   |
| TDHG     |

Desired output:

| id  | name     |
| --- | -------- |
| 1   | DFHG     |
| 2   | DRTYFGJN |
| 3   | FGH      |
| 4   | FGH      |
| 5   | NBHFGD   |
| 6   | OIUKJH   |
| 7   | RTFHGJ   |
| 8   | TDHG     |

Thank you in advance :)

what is the difference between input and output?Do you want to have row number in output? — zealous, Jun 01 '20 at 03:12
Does this answer your question? [MySQL - Get row number on select](https://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select) — Ersoy, Jun 01 '20 at 03:22

zealous · Answer 1 · 2020-06-01T07:29:30.150

2

First option is by using variable

set @rank=0;

select
    @rank:=@rank+1 as id,
    name
from myTable
order by 
    name;

Second option is by using row_number(), only if you are using MySQL 8.0.

select 
    row_number() over (order by name) as id,
    name
from myTable;

edited Jun 01 '20 at 07:29

answered Jun 01 '20 at 03:16

zealous

7,336
4
16
36

Your first query needs an `ORDER BY` clause otherwise it could produce a different result every time it is run. – Nick Jun 01 '20 at 03:27
I rather think that if the output doesn't match the desired result, then something's wrong somewhere – Strawberry Jun 01 '20 at 07:19
The variables in the first query are not guaranteed to follow the `order by`. – Gordon Linoff Jun 01 '20 at 12:12

SQL Function to Replace specific text with Count

1 Answers1