Julián H Julián H - 1 month ago 4
SQL Question

SQL Selecting the latest row using GROUP BY

An academic term that is using a semester system divides the year in two.

I've the following result from a SQL query, the term, as stated above, has its values from 1 to 2 always it also holds the score of the student (ID) for that specific term:

ID | Year | Term | Career | Score
-----------------------------------
1 | 2015 | 2 | 70 | 3,25
1 | 2015 | 1 | 70 | 3,58
2 | 2015 | 1 | 71 | 4,05
2 | 2014 | 2 | 71 | 4,17
3 | 2012 | 2 | 70 | 3,88
3 | 2012 | 1 | 70 | 4,23
3 | 2011 | 2 | 70 | 4,78


I want to return the corresponding row for the latest semester (year-term) for each student, so a result that would look like:

ID | Year | Term | Career | Score
-----------------------------------
1 | 2015 | 2 | 70 | 3,25
2 | 2015 | 1 | 71 | 4,05
3 | 2012 | 2 | 70 | 3,88


I'm making use of the GROUP BY clause with these particularities:


  • Grouping by the 'ID'

  • Selecting the MAX(Year)

  • Selecting the MAX(Term)



The problem is that I need to keep the term to the corresponding year and the MAX(Term) clause is the one that causes the mistake. For instance, using the query I got now, the result is not true because it would return this row:

ID | Year | Term | Career | Score
-----------------------------------
2 | 2015 | 2 | 71 | 4,05


Instead of this one (which is the correct one):

ID | Year | Term | Career | Score
-----------------------------------
2 | 2015 | 1 | 71 | 4,05


How can I keep the correspondence between the (year-term)?

As hint: 2015-2 is greater than 2015-1 and 2014-1 is greater than 2013-2.

Answer

Unfortunately, Informix does not offer row_number().

Does the following work?

select t.*
from t
where year || term = (select max(t2.year || t2.term)
                      from t t2
                      where t2.id = t.id
                     );
Comments