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

Navigation
6

The doubt is simple, but unfortunately I do not think the resolution. I have a table with several records, and I need to make 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].

    
asked by anonymous 20.01.2016 / 20:01

4 answers

6

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;
    
20.01.2016 / 20:11
0

I think you need to format for date and then group. 

 SELECT * FROM tabela GROUP BY DATE(data) ORDER BY data DESC
    
20.01.2016 / 20:04
0

Try using: 

 SELECT max(valor1) FROM tabela GROUP BY DATE(data)
    
20.01.2016 / 20:06
0

I believe you will have to search for different days and sort by date would look like this: 

SELECT DISTINCT DAY(data) as dia, t.* FROM tabela t ORDER BY data DESC
    
20.01.2016 / 20:08
Global Custom Variable Execute insert in Migrations Entity Framework