How to mount a SELECT to return the last status change of each id?

Asked

Viewed 1,004 times

8

I have the following table:

status_invoices

id_fatura | status | data
----------------------------------------
XX-XX-XX  |   3    | 2017-01-04 21:00:24
XX-XX-XX  |   2    | 2017-01-02 11:10:20
YY-YY-YY  |   4    | 2017-01-04 21:00:24
YY-YY-YY  |   1    | 2017-01-02 11:10:20
----------------------------------------

How can I mount a query to return me (id_invoice, status, date) of the last status change?

The result of the consultation should be something like:

id_fatura | status | data
----------------------------------------
XX-XX-XX  |   3    | 2017-01-04 21:00:24
YY-YY-YY  |   4    | 2017-01-04 21:00:24
----------------------------------------

How to do this in an SQL query? Thank you in advance!

mysql sql
  • 1

    This is cool, from DB to DB have different tricks to achieve this. In Sqlite, in recent versions, would be enough a MAX() in the column data to force the column status track (and this is a documented resource). In Mysql ORDER BY ... DESC works, but there is no promise to always be so in future verses.

  • So... I tried in ways that I thought would work, but it turned out that the status did not follow. Excellent trick kk

1 answer

8


A simple way would be to do this:

SELECT
  `id_fatura`, `status`, `data`
FROM
  `status_faturas`
GROUP BY
  `id_fatura`
ORDER BY
  `data` DESC

See working on SQL Fiddle.

in this case the ORDER BY data DESC makes the group always return to the date larger.

The problem is:


Safe and portable alternative:

With a little "ruse" you can do something a little different:

SELECT
   A.id_fatura,
   A.status,
   A.data
FROM
   status_faturas A
LEFT JOIN
   status_faturas B
   ON  A.id_fatura = B.id_fatura
   AND B.data > A.data
WHERE
   B.id_fatura IS NULL

See working on SQL Fiddle.


This works as follows:

  • First, we relate the table twice, and on the right side only the lines with the field will actually be returned data of a value greater than that of the left, for each id_fatura;

  • How we use LEFT JOIN, all left-hand columns will be returned at least once, even though they cannot satisfy the clause ON;

    Understand the reason in this post:

    What is the difference between INNER JOIN and OUTER JOIN?

  • And here comes the trick: with the WHERE we discard all that has a longer date, and what is left is only the newest of each group, which is our ultimate goal

Note: in the example above I used the data which was put in the question, but if it has two equal dates, the result is unpredictable (whatever the method used). It would be better, if you are launching everything in the correct time sequence, to use B.id > A.id, being id the autonumbering column of your table.

  • 1

    Perfect @Bacco :)

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.