mysql - How do I identify which id is used from a distinct list? -

i have following data

id -> company_name   -> chain -> subchain    -> number 1  -> account 555    -> 555   -> 555zz       ->  123450 2  -> account 745    -> 745   -> 745aa       ->  123451 3  -> account 745    -> 745   -> 745aa       ->  123452 4  -> account 745    -> 745   -> 745bb       ->  123453 5  -> account 745    -> 745   -> 745cc       ->  123454 6  -> account 555    -> 555   -> 555zz       ->  123455  

what trying list of column "number" , column "id" distinct list of columns "chain" , "subchain".

so need know number, id records has same chain , different subchain.

this code give me distinct list not number , id of once selected.

how can handle using mysql? select distinct chain, subchain records

the following result need

id -> company_name   -> chain -> subchain    -> number 1  -> account 555    -> 555   -> 555zz       ->  123450 2  -> account 745    -> 745   -> 745aa       ->  123451 4  -> account 745    -> 745   -> 745bb       ->  123453 5  -> account 745    -> 745   -> 745cc       ->  123454 

the id column primary column , number unique value chain value links/groups multiple records together. subchain differentiate group sub group

if can live arbitrary id, use group by:

select max(id) id, min(company_name) company_name, chain, subchain, min(number) number records group  chain, subchain; 

if need other 3 fields come same record (say 1 lowest id in example):

select min(id) id,        substring_index(group_concat(company_name order id), ',', 1) company_name,        chain, subchain,        substring_index(group_concat(number order id desc), ',', 1) + 0  number records group chain, subchain;