1

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!

0

2 Answers 2

4

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.

5
  • 4
    They probably want OVER (... ORDER BY CHRQDATE DESC, CHSCORE) but good answer. Jul 31, 2015 at 1:09
  • The example is actually standard SQL - no T-SQL specialties in there. It will run without any change on Oracle, Postgres and DB2
    – user1822
    Jul 31, 2015 at 6:13
  • Actually this will not compile in SQL Server, unless you specify an alias for the subselect.
    – Andriy M
    Jul 31, 2015 at 11:57
  • Un fortunately, this is MS Access SQL and the query suggested above results in the following error: Syntax error (missing operator) in query expression 'MIN(CHSCORE) OVER(PARTITION BY CHACCTBS)'. Jul 31, 2015 at 14:31
  • Made suggested fixes even though it won't work for original question based on updated platform.
    – Dave
    Aug 4, 2015 at 15:16
2
select
     RiskScore.CHACCTBS
    ,RiskScore.CHRQDATE
    ,Min(RiskScore.CHSCORE) as CHSCORE
from RiskScore
inner join (
    select
         RiskScore.CHACCTBS
        ,Max(RiskScore.CHRQDATE) as CHRQDATE
    from RiskScore
    group by RiskScore.CHACCTBS
    ) as t
    on (RiskScore.CHACCTBS = t.CHACCTBS)
    and (RiskScore.CHRQDATE = t.CHRQDATE)
group by RiskScore.CHACCTBS, RiskScore.CHRQDATE;
2
  • Fantastic! That was exactly what I needed. Aug 4, 2015 at 13:46
  • Glad you got him a working version for his platform!
    – Dave
    Aug 4, 2015 at 15:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.