CODEWITHSUNDEEP
sqlmariadbmariadb-10.4
tiwtiwtiwtiwaaaaa
tiwtiwtiwtiwaaaaa

Reputation: 108

select a column with how many same value present

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?

Upvotes: 0

Views: 35

Answers (1)

nbk
nbk

Reputation: 49375

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

Upvotes: 3

Related Questions