CODEWITHSUNDEEP
mysqlselectmax
AbsoluteHeero
AbsoluteHeero

Reputation: 175

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

Consider these two 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 i want to do is from each projectId, i want to get the max stageId value

So i want to end up with:

1-4

2-5

3-3

and assign the stageName from the stage table so the final result would 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
from stageProject a, stageTable b
where a.stageId=b.stageId
group by a.projectId

but it does not work

i end with the correct stageId and max but the stageName is always the same

Can you help me out !

Upvotes: 2

Views: 1505

Answers (2)

Lluis Martinez
Lluis Martinez

Reputation: 1973

Try this

select distinct projectid, 
       (select max(stageid) 
        from  stageProject p2
        where p1.projectId = p2.projectId),
       (selec stageName from stageTable 
        where stageId = (select max(stageid) 
                         from stageProject p2
                         where p1.projectId = p2.projectId))
from stageProject p1

or this

select ps.projectid, ps.stageid, st.stagename
from stageTable st, 
      (select a.projectid, max(a.stageid) stageid
       from stageProject a, stageTable b
       where a.stageId=b.stageId
       group by a.projectId) ps
where st.stageid = ps.stageid

Upvotes: 0

Filipe Silva
Filipe Silva

Reputation: 21657

Try something like this:

SELECT a.projectId,a.maxStageId,b.stageName
FROM (SELECT projectId,max(stageId) as maxStageId
      FROM stageProject 
      GROUP BY 1 ) a
INNER JOIN stageTable b ON a.maxStageId = b.stageId;

sqlfiddle demo

This will get the max stageID for each projectID, then take those results and do an inner join with stageTable to get their names.

Should do what you want.

Upvotes: 1

Related Questions