SQL to Get Latest Field Value

I’m trying to write an SQL query (SQL Server) that returns the latest value of a field from a history table.

The table structure is basically as below:

ISSUE TABLE:

issueid
10
20
30

CHANGEGROUP TABLE:

changegroupid | issueid | updated    |
1             | 10      | 01/01/2020 |
2             | 10      | 02/01/2020 |
3             | 10      | 03/01/2020 |
4             | 20      | 05/01/2020 |
5             | 20      | 06/01/2020 |
6             | 20      | 07/01/2020 |
7             | 30      | 04/01/2020 |
8             | 30      | 05/01/2020 |
9             | 30      | 06/01/2020 |

CHANGEITEM TABLE:

changegroupid | field   | newvalue |
1             | ONE     | 1        |
1             | TWO     | A        |
1             | THREE   | Z        |
2             | ONE     | J        |
2             | ONE     | K        |
2             | ONE     | L        |
3             | THREE   | K        |
3             | ONE     | 2        |
3             | ONE     | 1        | <--
4             | ONE     | 1A       |
5             | ONE     | 1B       |
6             | ONE     | 1C       | <--
7             | ONE     | 1D       |
8             | ONE     | 1E       |
9             | ONE     | 1F       | <--

EXPECTED RESULT:

issueid | updated    | newvalue
10      | 03/01/2020 | 1
20      | 07/01/2020 | 1C
30      | 06/01/2020 | 1F

So each change to an issue item creates 1 change group record with the date the change was made, which can then contain 1 or more change item records.

Each change item shows the field name that was changed and the new value.

I then need to link those tables together to get each issue, the latest value of the field name called ‘ONE’, and ideally the date of the latest change.

These tables are from Jira, for those familiar with that table structure.

I’ve been trying to get this to work for a while now, so far I’ve got this query:

SELECT issuenum, MIN(created) AS updated FROM 
(
    SELECT ISSUE.IssueId, UpdGrp.Created as Created, UpdItm.NEWVALUE
    FROM ISSUE
        JOIN ChangeGroup UpdGrp ON (UpdGrp.IssueID = CR.ID)     
        JOIN CHANGEITEM UpdItm ON (UpdGrp.ID = UpdItm.groupid)        
    WHERE   UPPER(UpdItm.FIELD) = UPPER('ONE') 

) AS dummy
GROUP BY issuenum
ORDER BY issuenum

This returns the first 2 columns I’m looking for but I’m struggling to work out how to return the final column as when I include that in the first line I get an error saying “Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.”

I’ve done a search on here and can’t find anything that exactly matches my requirements.

Answer

Use window functions:

SELECT i.*
FROM (SELECT i.IssueId, cg.Created as Created, ui.NEWVALUE,
             ROW_NUMBER() OVER (PARTITION BY i.IssueId ORDER BY cg.Created DESC) as seqnum
      FROM ISSUE i JOIN
           ChangeGroup cg
           ON cg.IssueID = CR.ID JOIN
           CHANGEITEM ci
           ON cg.ID = ci.groupid        
      WHERE UPPER(UpdItm.FIELD) = UPPER('ONE') 
    ) i
WHERE seqnum = 1
ORDER BY issueid;