Return to latest Date by column?

Navigation
1

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 to return only the products with the most recent dates, ie just these: 

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

I used this select, but it does not return anything:

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)
    
asked by anonymous 28.07.2017 / 23:52

3 answers

3

If you always want the items from 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'
  )

Is this what you wanted?

Example sqlfiddle

link

    
29.07.2017 / 00:03
3

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's longer, you'll only edit the range.

The query below returns all results that have nome_mercado as extra and the last records for 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
    
28.07.2017 / 23:58
2

Do not need subquery, just do it all together: 

SELECT * FROM mercado WHERE nome_mercado LIKE 'extra' ORDER BY data_compra DESC LIMIT 2;
    
29.07.2017 / 00:04
Manipulating list of lists in Python How to make a subquery in laravel eloquent with WHERE