Difficulty building an SQL statement

Navigation
2

I have broken my mind here to build a SQL, maybe someone can help me.

I'm trying to write a report to collect the relationship of notes that were eventually deleted.

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

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

For example, I have the following query: 

 SELECT id, nf, status, date FROM documentos

The statement returns the following records:

Asitshouldbe,fromtheaboveresultIwanttoknowonlythelastrecordofagivennfwhenitis"deleted" status.

I'm using Mysql to do the query.

    
asked by anonymous 01.09.2017 / 04:00

1 answer

5

I think you should have a simpler way of doing this, but that should 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% internal% groups the documents by SELECT and chooses only those with the highest nf within each group, thus bringing only the last record of each id . External%% then searches for documents with these nf s 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 those are deleted.

Edited: Fellow Rovann Linhalis put it in SQLFiddle .

    
01.09.2017 / 04:15
Routes accessible only to users logged in to Laravel? Get product id with Jquery