Select max date and the date before

Question

I have a data table which is updated every day except Saturday and Sunday. The problem lies in so that when I retrieve data with max (date) and max (date) -1

But it fails when I try to retrieve data for today (Monday) and yesterday (Sunday) when max (date) -1 does not exist.

the data can be updated on Saturday and Sunday, but since it's exchange rate I update. Will it give the same exchange rates Friday, Saturday and Sunday.

This is one way to solve the problem this way, but there is a better

string weekend = DateTime.Now.DayOfWeek.ToString();
if (weekend == "Monday")
{
select ***** where max(date)-3 from *****
}

score 1 · Answer 1 · answered Jul 05 '10 at 18:47

1

You can select the newest record that is at least one day old. Then the weekend isn't a special case.

answered Jul 05 '10 at 18:47

Mark Byers

811,555
193
1,581
1,452

score 1 · Answer 2 · answered Jul 05 '10 at 18:50

You are effectively trying to find the second highest date in a dataset.

Find the highest date in the data. Find values less than this, limit the results to 1 and you have the previous day in the dataset.

Any other method will fail on other days when rates are not updated, e.g. christmas.

score 0 · Accepted Answer · answered Jul 05 '10 at 18:50

0

Here's some other options which work with an arbitrary target_date as well.

If you only have one record for each date,

SELECT * FROM table WHERE date<=target_date ORDER BY date DESC LIMIT 2

If you have many records per date,

SELECT * FROM table WHERE date IN (SELECT DISTINCT date FROM table WHERE date<=target_date ORDER BY date DESC LIMIT 2)

You could also use the date/time functions of your database to check for Monday, of course.

answered Jul 05 '10 at 18:50

Dark Falcon

43,592
5
83
98

@Sigenstroem - But SELECT TOP x... would work as Dark Falcon has suggested. – Walter Jul 09 '10 at 18:51
Yes it works, it is also the approach I used to get it to work. So thank you for the answers.! – Sigenstroem Aug 03 '10 at 09:40

score 0 · Answer 4 · answered Jul 09 '10 at 18:41

hmm you might need som more details on the string

The sql string is

SELECT m.Navn, m.Beskrivelse, p_idag.Points AS Points_idag, p_igaar.Points AS Points_igaar FROM medlem m LEFT JOIN (SELECT * FROM point WHERE Datoen = '06-09-2010') AS p_idag ON m.Navn = p_idag.Navn LEFT JOIN (SELECT * FROM point WHERE Datoen = '06-08-2010') AS p_igaar ON m.Navn = p_igaar.Navn ORDER BY m.Navn;

The first date is selected by the user, the next date that date -1 day or -3

score 0 · Answer 5 · answered Jan 15 '21 at 02:38

0

An easy way that works for me is:

select * from yourtable where date=(select max(date) from yourtable where date < (select max(date) from yourtable))

Not sure how efficient it is

answered Jan 15 '21 at 02:38

Barry

1
3

Select max date and the date before

5 Answers5