0

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!

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
Carol.Kar
  • 4,581
  • 36
  • 131
  • 264
  • I have added [tag:greatest-n-per-group] to the question. You can find many more answers to your question [here](https://stackoverflow.com/search?q=%5Bgreatest-n-per-group%5D+and+%5Bmysql%5D) – Madhur Bhaiya Sep 04 '19 at 06:15

1 Answers1

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;
Dark Knight
  • 6,116
  • 1
  • 15
  • 37