I have a database table that contains testing scores. It consists of the following columns:
COMPANY, ID, DATE, TIME, SCORE (bold = key)
COMPANY is a given value. For this company I would like to retrieve the most recent testing scores for all available ID's.
Code: Select all
SELECT DISTINCT id MAX( date ) MAX( time ) score FROM <table> INTO <internal_table> GROUP BY date time score ORDER BY id ASCENDING.
My guess it that it tries to group by a unique combination of all fields in the GROUP BY clause. And indeed the combination of the three is unique... but I just want the record with the highest date, when there are multiple with the highest time.
Can this be done in a single query? Thanks in advance!