write SQL query

Question

MySQL

mysql database.

a table with the following data (simplified): I would like to get the following result: (the data is grouped by project_id and the line is taken where year is the minimum)

id____ project_id_____year
1________1____________2010
2________1____________2008
3________1____________2009
4________2____________2007
5________2____________2009



id________project_id_____year
2____________1___________2008
4____________2___________2007


Answer 1

Suppose the table is called project.
SELECT p1.id, p1.project_id, p1.year FROM project as p1 LEFT OUTER JOIN project as p2 ON (p1.project_id = p2.project_id AND p1.year > p2.year) where p2.project_id is NULL;
SELECT p1.id, p1.project_id, p1.year FROM project as p1 JOIN (SELECT p2.project_id, MIN(p2.year) AS min_year FROM project as p2 GROUP BY p2.project_id) as p3 ON (p1.year = p3 .min_year);

Answer 2

I will offer my 2 options:

SELECT ID, PROJECT_ID, YEAR
FROM PROJECT AS P1
JOIN (SELECT PROJECT_ID, MIN(YEAR) AS YEAR
    FROM PROJECT
    GROUP BY PROJECT_ID
    ) AS P2
ON P1.PROJECT_ID = P2.PROJECT_ID AND P1.YEAR = P2.YEAR

SELECT ID, PROJECT_ID, YEAR
FROM PROJECT
WHERE (PROJECT_ID, YEAR) IN
    (
     SELECT PROJECT_ID, MIN(YEAR) AS YEAR
     FROM PROJECT
     GROUP BY PROJECT_ID
    )

Answer 3

SELECT min(id), project_id, min(year) FROM project GROUP BY project_id