SQL query to find the last closed seq_id for each workorder

Question

As I am not expert in writing the SQL queries so want for help.

I have the below given dataset.

I need to write a query to find the "last closed seq_id for each workorder"

The final query should return rows only with seq_id = 24,28,32 enter image description here

Answer 1

Try this:

SELECT   sts
        ,workorder_id
        ,MAX(SEQ_ID) AS Last_Seq_ID
FROM mytablename
WHERE sts = 'Closed' -- Only include Closed records
GROUP BY sts, workorder_id
ORDER BY workorder_id -- This line is optional; it will sort your result

Answer 2

this will work

SELECT workorder_id
      ,max(seq_id) 
from   your_table 
where  sts="Closed" 
group by workorder_id

Answer 3

Assuming that you really mean "32" rather than "31", then row_number() is the typical method:

select t.*
from (select t.*,
             row_number() over (partition by workorder_id order by seq_id desc) as seqnum
      from t
      where sts = 'Closed'
     ) t
where seqnum = 1;

Answer 4

Try this:

select workorder_id,max(seq_id) as seqid
from testtable 
where sts='Closed'
group by workorder_id