Consult the last 3 days in which there was record in the table

Question

I have a table called OperacoesCaixa and in it I have the following fields:

Cod, Valor, DataOp

Well I need to make one select that I return the releases of the last 3 days, but I do not want to inform a date.

Assuming I have a launch held 15 days ago the select have to return the last 3 days.

I tried to do it this way:

SELECT
    *
FROM
    OperacoesCaixa
WHERE
    DataOp BETWEEN CURDATE() - INTERVAL 3 DAY AND CURDATE()
ORDER BY
    DataOp
DESC

The problem is that it picks up the current date, if there is no release in the last 3 days the select you won’t return anything to me.

-------- Edit --------

Good to facilitate I will post a real example.

We will use the date of 17-07-2020 as reference of the consultation day.

Example 1:

I have the following data in the table:

Cod Valor       DataOp

1   85.20       13-07-2020
2   97.14       13-07-2020
3   89          13-07-2020
4   100         15-07-2020
5   247.85      15-07-2020
6   58          16-07-2020
7   97          16-07-2020
8   86          16-07-2020
9   875         17-07-2020
10  85.20       17-07-2020

Good the select must return the following result:

10  85.20       17-07-2020
9   875         17-07-2020
8   86          16-07-2020
7   97          16-07-2020
6   58          16-07-2020
5   247.85      15-07-2020
4   100         15-07-2020

That is, the launch of the last 3 days.

Example 2 note that in the last 3 days there were no releases:

I have the following data in the table:

Cod Valor       DataOp

1   85.20       5-07-2020
2   97.14       5-07-2020
3   89          5-07-2020
4   100         8-07-2020
5   247.85      8-07-2020
6   58          9-07-2020
7   97          9-07-2020
8   86          10-07-2020
9   875         10-07-2020
10  85.20       10-07-2020

The answer must be:

10  85.20       10-07-2020
9   875         10-07-2020
9   875         10-07-2020
8   86          10-07-2020
7   97          9-07-2020
6   58          9-07-2020
5   247.85      8-07-2020
4   100         8-07-2020

Summarizing independent of today’s date I want to be listed all the records of the 3 days that there was record.

Answer 1

If it is the last 3 days, it would be easier to say "date greater than current date - 3", which in an SQL command would be:

WHERE DataOp > CURDATE() - INTERVAL 3 DAY

Okay, I’ll leave the previous query, because it might interest someone.

Now let’s get to the case of the question. Since you need to list all the values, picking up the latest dates, you need a subquery. If they were total, just group by date, order and take the last 3, like this:

select DataOp, SUM(Valor) from OperacoesCaixa
group by DataOp
order by DataOp DESC
limit 3;

Since you need to list everything, we can use this same query to select dates by removing the SUM of course and put in the main query:

select * from OperacoesCaixa
where DataOp in (select * from (
                 select DataOp from OperacoesCaixa
                  group by DataOp
                  order by DataOp DESC
                  limit 3) as sub)
order by DataOp desc; 

Note that, I added a second level in the subquery, because the direct subquery gives error when using the LIMIT, that here it is essential to pick up the 3 dates only.

See here working: https://www.db-fiddle.com/f/2X55LZBmqcFh7WHnaKMAfo/1

Answer 2

I think it’s something like that, I may have missed some detail of the syntax

--1
SELECT
    distinct DataOp
FROM
    OperacoesCaixa
order by DataOp desc    
    
--2 
select DataOp
from
(
SELECT
    distinct DataOp
FROM
    OperacoesCaixa
order by DataOp desc
) virtual limit 3

--3
select *
from 
where DataOp in
(select DataOp
from
(
SELECT
    distinct DataOp
FROM
    OperacoesCaixa
order by DataOp desc
) virtual limit 3
) virtual2