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

Question

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

Navigation

#1 by (7 votes)

7

I have the following table:

status_faturas

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 set up a query to return (id_file, status, date) from the last status change?

The result of the query 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 a SQL query? Thank you in advance!

mysql sql

asked by anonymous 05.01.2017 / 00:08

1 answer

Replace anchor by button in form with calculation by javascript How to calculate the median when the data is in chunks?

score 7 · Accepted Answer

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 in SQL Fiddle .

In this case, the ORDER BY data DESC causes the group to always return the largest date.

The problem is:

This is non-portable, MySQL-specific behavior
The manual itself says that this may change one day, in some newer version, in favor of better cluster optimization.

link

Safe and portable alternative:

With a little "trick" 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 in SQL Fiddle .

This works as follows:

Since we use data , all left columns will be returned at least once, even if we can not satisfy the id_fatura clause;

Understand the reason for this post:

What's the difference between INNER JOIN and OUTER JOIN?
And here comes the trick: with LEFT JOIN we discard all those with a bigger date, and what's left is just the youngest of each group, which is our final goal

Note: In the example above I used the ON that was put in the question, but if you have two dates equal, the result is unpredictable (whatever method is used). Best would be, if you are casting everything in the correct timestamp, use WHERE , being data the autonumber column of your table.