0

i have string data on my column

------------
name 
------------
john-yolo    
john-yolo    
john-yolo    
felix-goran  
carine-carin
carine-carin

i want to select name column with how many times the name present

ex :

------------
name 
------------
john-yolo-1    
john-yolo-2  
john-yolo-3    
felix-goran-1  
carine-carin-1
carine-carin-2

how can i produce data like that?

Will B.
  • 17,883
  • 4
  • 67
  • 69
tiwtiwtiwtiwaaaaa
  • 110
  • 9

1 Answers1

3

MaraiDB supports ROW_NUMBER

CREATE TABLE test
    (`name` varchar(12))
;
    
INSERT INTO test
    (`name`)
VALUES

    ('john-yolo'),
    ('john-yolo'),
    ('john-yolo'),
    ('felix-goran'),
    ('carine-carin'),
    ('carine-carin')
;

SELECT CONCAT(name,'-', ROW_NUMBER() OVER(PARTITION BY name)) as  name FROM test

| name           |
| :------------- |
| carine-carin-1 |
| carine-carin-2 |
| felix-goran-1  |
| john-yolo-1    |
| john-yolo-2    |
| john-yolo-3    |

db<>fiddle here

nbk
  • 45,398
  • 8
  • 30
  • 47
  • For others using MySQL 8.0 (as opposed to MariaDB), to prevent the `ER_BAD_FIELD_ERROR: Unknown column 'name' in 'window partition by'` error, you would want to add the table name or alias column identifier `PARTITION BY test.name` [Db-Fiddle Example](https://www.db-fiddle.com/f/rAtPJLFZCmV1tRTGZYCZV7/0) – Will B. Oct 14 '21 at 18:10
  • @WillB. name is no reserved word so the query runs as expected https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ef6652425a35cfaeb097f97182d65977 – nbk Oct 14 '21 at 18:20
  • Check the example I provided that is throwing the error and the one that is not. It's not about the reserved name, but about the aliased column name. As it works fine outside of `CONCAT()`. The escaped sequence alias for the second query is not needed but just was added by habit. – Will B. Oct 14 '21 at 18:28
  • @WillB. the site uses 8.0.12 that is very old to old for my taste – nbk Oct 14 '21 at 18:32
  • None-the-less still applicable to prevent the error when it arises. – Will B. Oct 14 '21 at 18:33