to create SQL query

0 like 0 dislike
29 views
The database is mysql.

a table with the following data (simplified):

id___ _ _ project_id___ _ __year
1________1____________2010
2________1____________2008
3________1____________2009
4________2____________2007
5________2____________2009


I would like to get such result:
(data grouped by project_id and taken the line where year — minimum)

id________project_id___ _ __year
2____________1___________2008
4____________2___________2007
by | 29 views

3 Answers

0 like 0 dislike
Assume the table is called project.
\r
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;
\r
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);
by
0 like 0 dislike
Offers 2 options:
\r
\r
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

\r
\r
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 )
by
0 like 0 dislike
SELECT min(id), project_id, min(year) FROM project GROUP BY project_id
by

Related questions

0 like 0 dislike
3 answers
asked May 22, 2019 by Dvoeglazov
0 like 0 dislike
1 answer
asked May 20, 2019 by Dvoeglazov
0 like 0 dislike
1 answer
0 like 0 dislike
2 answers
asked Apr 4, 2019 by AronTito
0 like 0 dislike
2 answers
asked Jun 3, 2019 by KRHD
110,608 questions
257,186 answers
0 comments
28,804 users