Minimum value on most recent date per account MS ACCESS SQL I have a MS ACCESS DB table Riskscore with the three fields: CHACCTBS, CHRQDATE, CHSCORE and want to find the lowest CHSCORE on the most recent CHRQDATE per CHACCTBS where it is possible to have multiple CHSCORE values on the same CHRQDATE.  There should be one record returned for each CHACCTBS.  There are about 17,000 records.  Once the query has run, there should be 7008 records returned.

CHACCTBS, CHRQDATE, CHSCORE
101    1/1/2014    781
101    1/1/2014    791
101    12/31/2013   717
221    10/01/2013   512
235    1/2/2014     685
235    10/31/2013   600


The query should return:

101    1/1/2014    781
221    10/01/2013   512
235    1/2/2014     685


Thanks!
This would be a good place to use window functions if your RDBMS supports them. The example below is T-SQL so if you are not on SQL Server you may have to adapt the syntax and/or keywords.

Window Functions are ANSI Standard starting with SQL:2003, and as such you likely have access to them with any modern RDBMS. If you are on SQL Server read about their implementation of Window Functions here.

SELECT CHACCTBS, CHRQDATE, CHSCORE
FROM
(
    SELECT
         MIN(CHSCORE) OVER(PARTITION BY CHACCTBS) As CHACCTBS
        ,CHRQDATE
        ,CHSCORE
        ,ROW_NUMBER() OVER(PARTITION BY CHACCTBS ORDER BY CHRQDATE DESC, CHSCORE) As Limiter
    FROM RiskScore
) X
WHERE Limiter = 1


The subquery returns your MIN and other columns, and creates a filter by assigning row numbers by your partition and ordered by descending dates. You then take the first row of each partition in the WHERE clause.