How would I do this query?
Posted on 2014-12-31
Here's my sql:
select belt.student_id, belt.belt, belt.date, students.id, students.first_name, students.last_name from belt inner join students on belt.student_id=students.id where belt.belt='$belt' and students.status='Active' order by students.last_name
Slam dunk! No problem!
But, my "belt" table has several rows per student with a unique data attached to each row. I want to select from the "belt" table only the most recent "belt."
So, say I want to see all of my red belts. In the "belt" table, I'll have a student listed like this:
George Bush | Black Belt | December 5, 2014
George Bush | Red Belt | October 14, 2014
In this instance, I don't want George to be listed in my recordset because while he WAS a red belt back in October, he is now a Black Belt and I want to see only those who are currently a Red Belt.
How do I structure my select statement to achieve that?