mySQL Select All where date is less than 1 day from max date in data

I am having a table name “raw”.
The date column is not having all the dates of month as it does not record holidays or when there were no sales for the day.
I am trying to get the data form the date less than a day of specific date or max date but it is not returning the search as before the max date was holiday.
here are the dates:
2021-07-09
2021-07-07
2021-07-03
2021-07-02
2021-07-01
2021-06-30

SELECT *
    FROM raw
    WHERE date=(SELECT MAX(date) - 1 FROM raw);

or

SELECT *
FROM raw
WHERE date=(SELECT MAX(date) - interval 1 day FROM raw);

    begin{table}[]
begin{tabular}{lll}
Name  & Date       & Sales \
ACC   & 2020-07-09 & 2000  \
BEL   & 2020-07-09 & 200   \
Dabur & 2020-07-09 & 600   \
ACC   & 2020-07-07 & 450   \
GMR   & 2020-07-07 & 12    \
ACC   & 2020-07-03 & 450   \
ITC   & 2020-07-03 & 45    \
Dabur & 2020-07-03 & 350   \
GMR   & 2020-07-03 & 450   \
BEL   & 2020-06-30 & 500   \
GTL   & 2020-06-30 & 850   \
Dabur & 2020-06-30 & 100   \
ACC   & 2020-06-27 & 50    \
Dabur & 2020-06-27 & 125  
end{tabular}
end{table}

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

If you want the data from the day before a specific date, then use:

SELECT r.*
FROM raw r
WHERE r.date = (SELECT MAX(r2.date) FROM raw r2 WHERE r2.date < ?);

The ? is the specific date.

For the date previous to the maximum date, you could use:

SELECT r.*
FROM raw r
WHERE r.date = (SELECT MAX(r2.date)
                FROM raw r2
                WHERE r2.date < (SELECT MAX(r2.date) FROM raw r3)
               );

Ouch. Two levels of subqueries. That seems cumbersome. A simpler method is:

SELECT r.*
FROM raw r
WHERE r.date = (SELECT DISTINCT r2.date
                FROM raw r2
                ORDER BY r2.DATE DESC
                LIMIT 1 OFFSET 1
               );

Method 2

The query posted by TheWinterCoder should work perfectly if you just change the Order by clause to

rank() over ( order by date desc) date_rank

However, If that somehow doesn’t work, You may try below query –

SELECT *
    FROM raw
    WHERE date = (SELECT MAX(date)
                    FROM raw
                   WHERE date < (SELECT MAX(date) FROM raw));

Method 3

Since you have breaks in your dates, you can’t just subtract 1 from the max date. You would need to rank the dates and then select the top two ranks.

This should help and your query will look something like this:

with order_dates AS(
      Select *,
             dense_rank() over ( order by date desc) date_rank
      from raw
)
select *
from order_dates
where date_rank <=2


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x