mysql - How to select max value for each id in sql select statement? -

consider these 2 tables.

first table

---

stagetable

stageid-----stagename

1-----------start

2-----------planning

3-----------working

4-----------review

5-----------closing

---

second table

---

stageproject

stageid-------projectid

1-------------1

2-------------1

3-------------1

4-------------1

1-------------2

2-------------2

3-------------2

4-------------2

5-------------2

1-------------3

2-------------3

3-------------3

---

what want each projectid, want max stageid value

so want end with:

1-4

2-5

3-3

and assign stagename stage table final result be

projectid-----max-----stagename

1 4 review

2 5 closing

3 3 working

i've tried with

select a.projectid, max(a.stageid), b.stagename stageproject a, stagetable b a.stageid=b.stageid group a.projectid 

but not work

i end correct stageid , max stagename same

can me out !

try this:

select a.projectid,a.maxstageid,b.stagename (select projectid,max(stageid) maxstageid       stageproject        group 1 ) inner join stagetable b on a.maxstageid = b.stageid; 

sqlfiddle demo

this max stageid each projectid, take results , inner join stagetable names.

should want.