greatest n per group - one to one result in mysql Query -

i have 2 table:

• career
• career_details

table "career" contain fields (careerid,job_code)
table "career_details" contain fields (id, careerid, name)

let values [(1,code1),(2,code2)]--- career(table)
let values

[   ('1','1','codename1'),      ('2','1','codename11'),      ('3','2','codename22'),      ('4','2','codename222')   ] 

-- career_details(table)

now if write query("select * career c left join career_details cd on c.career_id=cd.career_id 1"), give result 4 rows need result 2 rows i.e

[(1,code1,1,1,codename1),(2,code2,3,2,codename22)] 

instead of writing left join, should write inner join

again per requirement, need first career details data first assigned career data. please refer below link. http://www.sqlfiddle.com/#!2/d1c64/14

select c.id,c.name name ,cd.id cdl_id,cd.career_id,cd.name dtl_name career_details cd  inner join careers c  on c.id=cd.career_id , cd.id in (select max(id) career_details group career_id);