Difficulty mounting an SQL statement

Question

I’ve been cracking my head here to mount an SQL, maybe someone can help me.

I’m trying to draft a report to charge the relationship notes that were eventually deleted.

The user registers an nf in the system that has as initial status "related", but in some cases he needs to delete this nf that goes from "related" status to "deleted", and makes another, a new record is made for that same nf, with different id, but with nf number equal to previous but now with status "related".

Finally, I wanted an sql query that is considered the last record of an nf that has status of "deleted".

For example, I have the following query:

 SELECT id, nf, status, date FROM documentos

The statement returns the following records:

As it should be, from the above result I want to know only the last record of a certain nf when it is status "deleted".

I’m using Mysql to query.

Answer 1

I believe there must be a simpler way to do this, but it must work:

SELECT u.id, u.nf, u.status, u.date
FROM documentos u
WHERE u.id IN (
    SELECT MAX(d.id)
    FROM documentos d
    GROUP BY d.nf
)
AND u.status = 'EXCLUIDO'

The SELECT group together the documents by nf and only choose the ones with the biggest id within each group, thus bringing only the last record of each nf. The SELECT external search then for the documents with these ids and filters only those with "deleted" status. As a result, you will only have the records that are the last of each NF, as long as these are deleted.

Edited: Comrade Rovann Linhalis put this on Sqlfiddle.