Return Most recent date per Column?

Question

I have this table in `Mariadb [accounts]>

SELECT * FROM mercado WHERE nome_mercado LIKE 'extra';

---

+----+--------------+-------------+-----------+-----------+-------+
| id | nome_mercado | data_compra | produto   | descricao | preco |
+----+--------------+-------------+-----------+-----------+-------+
|  1 | Extra        | 2017-07-12  | Coca Cola | Coca      |  3.00 |
|  2 | Extra        | 2017-07-12  | Sucos     | Suco      |  3.50 |
|  3 | Extra        | 2017-07-12  | Frios     | Frios     |  7.80 |
| 11 | extra        | 2017-07-28  | Suco      | Teste     |  5.90 |
| 12 | extra        | 2017-07-28  | Bolacha   |           |  2.49 |
+----+--------------+-------------+-----------+-----------+-------+`

I would like you to return only the products with the latest dates, that is, only these:

| 11 | extra        | 2017-07-28  | Suco      | Teste     |  5.90 |
| 12 | extra        | 2017-07-28  | Bolacha   |           |  2.49 |

I used this select, but it returns nothing:

Mariadb [accounts]>

SELECT * FROM mercado WHERE nome_mercado LIKE 'extra' = 
  (SELECT data_compra FROM mercado ORDER BY data_compra DESC LIMIT 1);

Empty set, 5 warnings (0.00 sec)

Answer 1

You can create a WHERE to return the results of the last 3 days, for example:

WHERE data_compra >= DATE(NOW()) - INTERVAL 3 DAY

If it is longer, only you edit the interval.

To query returns all results that have the nome_mercado as extra and the last records of the last 3 days. See:

SELECT * FROM mercado WHERE nome_mercado LIKE 'extra' AND data_compra >= DATE(NOW()) - INTERVAL 3 DAY ORDER BY data_compra DESC

Answer 2

If you always want the items of the last purchase do so:

select 
  * 
from 
  MERCADO 
where 
  NOME_MERCADO like 'extra'
  and DATA_COMPRA = 
  (
      select 
        max(DATA_COMPRA) 
      from 
        MERCADO 
      where 
        NOME_MERCADO like 'extra'
  )

This is what you wanted?

Example sqlfiddle

http://sqlfiddle.com/#! 9/dc427d/1

Answer 3

No need to subquery, just do everything together:

SELECT * FROM mercado WHERE nome_mercado LIKE 'extra' ORDER BY data_compra DESC LIMIT 2;