I made a change to my SQL tables but I am having trouble changing my query to acommodate that change. This query works:
select members.playername, ratings.rating, MAX(ratings.DATE), ratings.type from roles
left join ratings on roles.fk_member_id=ratings.member_ID_FKleft join members on roles.fk_member_id=members.pk_id
where roles.fk_member_id in (SELECT ROLES.FK_MEMBER_ID FROM ROLES WHERE ROLES.TYPE = 'Player' And ratings.type='8' AND FK_TEAMBAR_NUMBER = 109)group by playername, date, ratings.type, ratings.rating
The change is that now my application tracks historical data on a member's rating. So now there are multiple records for each member. I want my query to return a table of the entire team members and their most recent rating (most recent date). The above query will pull all ratings for each team member. I just need the most recent rating - therein lies my problem.
Here are the table structures:
RATING_ID intMEMBER_ID_FK intDATE smalldatetimeSET_ID_FK intRATING
View Complete Post