问题描述:

table: estimates

id | team_id | project_id | value | type

What I want to achieve is only the latest value of one estimate type.

Types are 1, 2 or 3

Lets say we have project_id 1 with estimate type 1, team_id 8 with a value 20 and another one with project_id 1 team_id 8 type 2 and a value 80

The only thing I want to see is the value 80 of project_id 1 for team 8.

Any ideas?

网友答案:
SELECT e.value, e.team_id, e.project_id
  FROM estimates e
INNER JOIN ( SELECT MAX(type_id) AS type_id
                  , team_id
                  , project_id
               FROM estimates 
           GROUP BY team_id, project_id
           ) mx ON (    mx.team_id = e.team_id 
                    AND mx.project_id = e.project_id
                    AND mx.type_id = e.type_id
                   )  
相关阅读:
Top