Select last status for ticket number-CodePudding

My table is:

I need to select all column by last status_changes. Result would be:

i tried this code:

with t_lastchanges as (
        SELECT
            issue_number,
            MAX (status_change_date) as lastchanges
        FROM
            table
        group by
            issue_number        
)

SELECT  DISTINCT  
    jvi.issue_number, jvi.current_status , t_lastchanges.lastchanges  
FROM
    table jvi
inner join t_lastchanges on
        jvi.status_change_date = t_lastchanges.lastchanges  and t_lastchanges.issue_number = jvi.issue_number 
ORDER BY 
    jvi.issue_number

but it doesn't work

CodePudding user response：

In most SQL dialects, when using aggregates (like max), you need to use group by.

SELECT project,
       issue_number,
       creation_date,
       assignee_,
       current_status,
       max(status_change_date)
FROM table
GROUP BY project,
         issue_number,
         creation_date,
         assignee_,
         current_status

Should work.

CodePudding user response：

I would use a window function to find the latest date per issue_number:

select *
from (
  select t.*
         dense_rank() over (partition by t.issue_number 
                            order by t.status_change_date desc) as rnk
  from the_table t
) as ranked
where rnk = 1;

Note that the result will include the rnk column. You will need to explicitly list all columns in the outer SELECT that you want, if you want to exclude that.