How to get the issue previous status using SQL query?

[Michał Gawlik]

Hi there,

Is a better way to get the previous state than getting it as the last row from the SQL query below?

SELECT 
TO_CHAR(grpit.newstring) AS new_state,
TO_CHAR(grpit.oldstring) AS old_state
FROM changegroup grp,
changeitem grpit
WHERE grp.id = grpit.groupid
AND grpit.field = 'status'
AND grp.issueid = <id>;

Thank you in advance!

Michal

[Norman Abramovitz · Accepted solution]

I would add the changeitem.ID column to select list  and order by 'changeitem.ID desc'  I would then add a top 1 clause to only get back the first row.