I have two tables, Year and FeesYear:
YEAR
| idyear(pk) | yearname |
|---|---|
| 1 | 10-11 |
| 2 | Summer 11 |
| 3 | 11-12 |
| 4 | Summer 12 |
| 5 | 12-13 |
| 6 | Summer 13 |
FEESYEAR
| idfees | idyear | idfeesyear(pk) |
|---|---|---|
| 1 | 1 | 1 |
| 1 | 2 | 2 |
| 1 | 3 | 3 |
| 1 | 5 | 4 |
| 2 | 2 | 5 |
| 2 | 3 | 6 |
| 2 | 4 | 7 |
| 3 | 1 | 8 |
| 3 | 2 | 9 |
| 3 | 3 | 10 |
What I would like is the highest (feesyear.idyear) value for an idfees value with the yearname (from the year table) returned on the same result record, so in this example results would be:
| idfees | idyear | yearname |
|---|---|---|
| 1 | 5 | 12-13 |
| 2 | 4 | Summer 12 |
| 3 | 3 | 11-12 |
It's including the yearname that's messing up my results the way I tried writing it. Please help if you can.
I tried the following but I've got things muddled going by the massive results returned:
select sf.idfees, yearname, sf.idyear from feesyear sf
inner join year y on y.idyear=sf.idyear,
(Select MAX(idyear) as maxyear, idfees
from feesyear
group by idfees) maxresults
where sf.idfees=maxresults.idfees
and sf.idyear=maxresults.maxyear;
This returned far too many records with a lot of duplicate looking records and simply didn't return what I wanted as I wrote above.
