How to make a query that returns the last record of each day?

Question

The doubt is simple, but unfortunately I don’t think the resolution. I have a table with several records, and I need to perform a query that returns the last record of each day of the last 20 days for example.

Exemplo:
|------------ teste ---------------|
|id  | valor1 | data               |
|   1|     12 | 2016-01-01 00:41:00|
|   2|     31 | 2016-01-01 23:00:12|
|   3|     34 | 2016-01-02 00:41:00|
|   4|     63 | 2016-01-03 05:20:21|
|   5|     87 | 2016-01-03 13:51:00|
|   6|     95 | 2016-01-03 14:00:00|

The search should return the record [2, 3, 6].

Answer 1

Try this solution:

SELECT * FROM tabela INNER JOIN (SELECT MAX(data) AS ultimo_registo FROM tabela GROUP BY DATE(data)) as Lookup ON Lookup.ultimo_registo = tabela.data

As you can see, I ran the test here and it worked:

Returning only the last 20 records:

SELECT * FROM minha_tabela INNER JOIN (SELECT MAX(data1) AS ultimo_registo
FROM minha_tabela GROUP BY DATE(data1)) as Lookup
ON Lookup.ultimo_registo = minha_tabela.data1 ORDER BY data1 DESC LIMIT 20;

Answer 2

I believe you need to format to date and then group.

 SELECT * FROM tabela GROUP BY DATE(data) ORDER BY data DESC 

Answer 3

Try to use:

 SELECT max(valor1) FROM tabela GROUP BY DATE(data) 

Answer 4

I believe you will need to search for different days and order the date would look like this:

SELECT DISTINCT DAY(data) as dia, t.* FROM tabela t ORDER BY data DESC