0

I have a table with date and Etotalday.

What I want to select is the last value of the last 7 days. Many solutions I looked for gives the highest value of a day which is not always the same as the last value.

In my case table is build with values and the day starts with the highest value of the day before and at about 06.00 new values are added.

Is there somebody who can gives me a hint how to do this? thnks

Lookes like duplicate question but I want the last value not the highest value and not the max value.

Henry
  • 111
  • 3
  • Can you show sample table data, what you are expecting as a query result, and what attempts you have made thus far? – Mike Brant Aug 16 '14 at 12:08
  • Following sample data where I try to explain the issue. At start of the day there is a higher value than at the end of the day while I want to display the last value of that day (which is actual)date DayTotalaccu 2014-08-10 23:50:01 26,2 2014-08-10 23:55:02 26,2 2014-08-11 00:00:02 26,2 2014-08-11 00:05:02 26,2 2014-08-11 00:10:02 26,2 2014-08-11 00:15:02 26,2 2014-08-11 00:20:02 26,2 2014-08-11 00:25:02 26,2 ...... 2014-08-11 23:50:02 23,5 2014-08-11 23:55:02 23,5 2014-08-12 00:00:02 23,5 2014-08-12 00:05:02 23,5 2014-08-12 00:10:02 23,5 – Henry Aug 16 '14 at 12:34
  • So the value I want is : 2014-08-11 23:55:02 23,5 which is the last value of day 2014-08-11. Table is called PacData with columns date and DayTotalaccu – Henry Aug 16 '14 at 13:02

1 Answers1

0

You can do this with order by and limit

select t.*
from PacData t
where date >= now() - interval 7 day
order by date;
limit 1;

If you want to return the last value for each of the past seven days (which might be the intent of the question), then here is one method:

select t.*
from PacData t
where not exists (select 1
                  from PacData t2
                  where date(t2.date) = date(t.date) and t2.date > t.date
                 );

This says: "Get me all records from the table where there is not record on the same date with a larger value from the date time column". By the way, date is a lousy name for a column that stores both a date and a time.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786