Get last updated rows

Question

I am using mysql - 10.1.39-MariaDB - mariadb.org binary distribution - and phpmyadmin for managing my db:

I have the following table:

| ID  | NAME       | AMOUNT | CREATED_AT          | UPDATED_AT          |
|-----|------------|--------|---------------------|---------------------|
| 1   | Product 1  | 4      | 2019-08-30 19:03:16 | 2019-08-30 19:03:16 |
| 5   | Product 1  | 3      | 2019-08-29 20:03:16 | 2019-08-29 20:03:16 |
| 2   | Product 2  | 4      | 2019-08-30 19:03:16 | 2019-08-30 19:03:16 |
| 3   | Product 33 | 3      | 2019-08-30 19:03:16 | 2019-08-30 19:03:16 |
| 4   | Product 4  | 2      | 2019-08-30 19:03:16 | 2019-08-30 19:03:16 |
| ... | ...        | ...    | ...                 | ...                 |

I would like to have all rows that have been updated last.

I tried the following:

SELECT
    `name`, `updatedAt`
FROM
    issuers
ORDER BY
    updatedAt

However, I still get all rows in my result data.

Any suggestions what I am doing wrong?

I appreciate your replies!

Answer 1

Reference: The Row Holding the Maximum of a Certain Column

Using a subquery which finds max updatedAt and compares it will all rows.

SELECT name, updatedAt
FROM   issuers
WHERE  updatedAt = (SELECT MAX(updatedAt) FROM issuers);

or using a LEFT JOIN;

SELECT s1.name, s1.updatedAt
FROM issuers s1
LEFT JOIN issuers s2 ON s1.updatedAt < s2.updatedAt
WHERE s2.updatedAt IS NULL;