MYSQL - create single record out of similar rows, chose values of greatest length for most columns

Question

Here is my case, I have a database table with below fields:

name
place_code
email
phone
address
details
estd
others

and example data

If you look at the above example table, first three records are talking about xyz and place code 1020.

I want to create a single record for these three records based on

substring(name,1,4) 
place_code 

(I am lucky here for all the similar records satisfies this condition and unique in the table.)

For the other columns which record column length has max. For example again for the above 3 records email should be [email protected], phone should be 657890 and details should be "testdetails".

This should be done for all the table. (Some has single records and some has max 10 records.)

Any help on query that helps me to get the desired result?

Answer

Some one posted the below answer and deleted it . But that looks a good solution

SELECT max(name),
       place_code,
       max(email),
       max(phone),
       max(address),
       max(details),
       max(estd),
       max(others)
FROM table_x
GROUP BY substring(name,1,4),place_code

Please let me know if you guys see any issues in it ?

Thank You all

Kiran

Answer 1

SELECT max(name),
       place_code,
       max(email),
       max(phone),
       max(address),
       max(details),
       max(estd),
       max(others)
FROM table_x
GROUP BY substring(name,1,4),place_code