How do I query for the most recent modified record?

Can someone help me to pull just the most recent modified record for a voter id.  I thought using max would do it but I am obviously wrong.  My query is below.

SELECT   JURISDICTIONS.NAME,
      VOTER_AUDITS.ID,
      VOTER_AUDITS.VTR_ID,
      MAX(VOTER_AUDITS.MODIFIED_DATE),
      VOTER_AUDITS.OLD_VALUE,
      VOTER_AUDITS.NEW_VALUE,
      VOTER_AUDITS.AUDTYP_ID
 FROM      JURISDICTIONS,VOTER_AUDITS, VOTERS
WHERE    JURISDICTIONS.CODE = VOTER_AUDITS.JS_CODE
  AND       VOTER_AUDITS.js_code ='46'
  AND       VOTER_AUDITS.AUDTYP_ID='7'
  AND       VOTER_AUDITS.VTR_ID = VOTERS.ID
GROUP BY JURISDICTIONS.NAME,
      VOTER_AUDITS.ID,
      VOTER_AUDITS.VTR_ID,
      VOTER_AUDITS.OLD_VALUE,
      VOTER_AUDITS.NEW_VALUE,
      VOTER_AUDITS.AUDTYP_I
restockettAsked:
Who is Participating?
 
sdstuberCommented:
SELECT name,
       id,
       vtr_id,
       modified_date,
       old_value,
       new_value,
       audtyp_id
  FROM (SELECT jurisdictions.name,
               voter_audits.id,
               voter_audits.vtr_id,
               voter_audits.modified_date,
               voter_audits.old_value,
               voter_audits.new_value,
               voter_audits.audtyp_id,
               ROW_NUMBER()
                   OVER (PARTITION BY voter_audits.vtr_id ORDER BY voter_audits.modified_date DESC)
                   rn
          FROM jurisdictions, voter_audits, voters
         WHERE     jurisdictions.code = voter_audits.js_code
               AND voter_audits.js_code = '46'
               AND voter_audits.audtyp_id = '7'
               AND voter_audits.vtr_id = voters.id)
 WHERE rn = 1
0
 
restockettAuthor Commented:
Thanks, that works.  I have more questions about how to use this as a subquery for another query but i will enter as a new Question
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.