-1

My table looks like this :

MySQL table

I want to find the id having max age from each skills in skills column. So the answer for this table will be Cooking->r1, Eating->r2 and Coding->r3.

How can I accomplish this?

as2d3
  • 802
  • 2
  • 10
  • 27
  • I'd recommend GROUP BY in a sub-query to find each skill's max age. Join with that result. – jarlh Sep 08 '17 at 12:40
  • @jarlh Yes, GROUP BY gives skills and max(age) but I want the corresponding id from max(age). – as2d3 Sep 08 '17 at 12:43
  • As I said, JOIN your table with the GROUP BY result! – jarlh Sep 08 '17 at 12:45
  • Just a note, as a naming convention I prefer column names in singularis, e.g. skill. (And table names in pluralis.) – jarlh Sep 08 '17 at 12:49
  • You have no ties in your sample data. If you have ties, how do you want them handled? Display all ID's having that max age? display the min(ID)? Something else? – xQbert Sep 08 '17 at 12:53

1 Answers1

2

Do a GROUP BY in a sub-query to find each skill's max age. JOIN with that result.

SELECT t.id, t.age, t.skills
FROM tablename t
join (select skills, max(age) as maxage
      from tablename
      GROUP BY skills) t2
    on t.skills = t2.skills and t.age = t2.maxage

Note: Ties will be included. (If there are two id's with same max age for a skill, both will be returned.)

jarlh
  • 42,561
  • 8
  • 45
  • 63