Mārcis Liepiņš Mārcis Liepiņš - 7 months ago 11
SQL Question

How to create unique index for each player MySQL?

I have a table like this!

T1
Name Surname Team
John Fire Team1
Mike Ice Team2
Don Grass Team3
John Fire Team4
Jake Finger Team1


I want to create and add to this table column named ID!

ID would consist of:

'first letter of Name'+

'first letter of Surname'+

'-'+

Number automatically increasing if there have been same first letters before!

It would look like this!

T1
ID Name Surname Team
JF-1 John Fire Team1
MI-1 Mike Ice Team2
DG-1 Don Grass Team3
JF-1 John Fire Team4
JF-2 Jake Finger Team1


What i need to add to this code?

#LAST TRY

create table T1 (
Name varchar(45),
Surname varchar(45),
Team varchar(45));

insert into T1(Name,Surname,Team) values ( 'John','Fire','Team1'), ('Mike','Ice','Team2'),
('Don','Grass','Team3'),('John','Fire','Team4'),('Jake','Finger','Team1');

select * from T1;

Answer

Basic approach for Id creation :

select * from(
select substring(Name,1,1), substring(Surname,1,1),
row_number() over (partition by Name,Surname order by Team)) from t1;

Will check syntax and expand.

Approach : Select first letter from first name, second letter from second name, and then based on grouping of name and surname, decide the number to be inserted.

EDIT : Try this as an implementation for MySQL.

select concat(concat(concat(SUBSTRING(NAME,1,1),SUBSTRING(SURNAME,1,1)),'-'),num) AS ID,
NAME,SURNAME,TEAM from

                            (SELECT 
    @row_number:=CASE
        WHEN @customer_no = SUBSTRING(NAME,1,1) and @customer2 = SUBSTRING(SURNAME,1,1)
        THEN @row_number + 1
        ELSE 1
    END AS num,
    @customer_no:=SUBSTRING(NAME,1,1) as CustomerNumber,
    @customer2 :=  SUBSTRING(SURNAME,1,1) as Customer2,
    @team := team                         
    team,name,surname
FROM
    T1 order by SUBSTRING(NAME,1,1), SUBSTRING(SURNAME,1,1),TEAM) as final;

Phew!! http://www.mysqltutorial.org/mysql-row_number/ is the reference link. But really MySQL!! Why can't you just give us basic analytic functions!!

Fiddle : http://sqlfiddle.com/#!2/edb8b2/7