CODEWITHSUNDEEP
mysqlsql
J. Ordaz
J. Ordaz

Reputation: 423

Getting last values for week in mysql

I would like know how to get the last value for each week.

Let's say I have the next values

-- Table 1 --
day          value
2018-03-12   32
2018-02-14   42
2018-03-16   62
2018-03-19   82
2018-03-20   92
2018-03-21   102
2018-03-27   112
2018-03-28   122
2018-03-29   132

How can I get the next values which are the last values for each week. Assuming the week start on Monday.

Day        Value
2018-03-16 62
2018-03-21 102
2018-03-29 132

I have everything settled here SQL Fiddle

Upvotes: 1

Views: 314

Answers (5)

Vanojx1
Vanojx1

Reputation: 5574

Try this FIDDLE:

= Order by the closest to the end of every week

= Group by week

SELECT day, value
FROM (SELECT * FROM table1 ORDER BY DATEDIFF(day + INTERVAL 6 - weekday(day) DAY, day) ASC) t
GROUP BY week(day);

Upvotes: 1

McNets
McNets

Reputation: 10807

You can group by YEARWEEK()

create table tbl (day date, value int);

✓

insert into tbl values
('2018-03-12',   32),
('2018-02-14',   42),
('2018-03-16',   62),
('2018-03-19',   82),
('2018-03-20',   92),
('2018-03-21',   102),
('2018-03-27',   112),
('2018-03-28',   122),
('2018-03-29',   132);

✓

select day, yearweek(day) from tbl;

day        | yearweek(day)
:--------- | ------------:
2018-03-12 |        201810
2018-02-14 |        201806
2018-03-16 |        201810
2018-03-19 |        201811
2018-03-20 |        201811
2018-03-21 |        201811
2018-03-27 |        201812
2018-03-28 |        201812
2018-03-29 |        201812

select day, value
from tbl
join (select max(day) mday
      from   tbl
      group by yearweek(day)) t
on day = mday

day        | value
:--------- | ----:
2018-02-14 |    42
2018-03-16 |    62
2018-03-21 |   102
2018-03-29 |   132

dbfiddle here

Upvotes: 1

jose_bacoy
jose_bacoy

Reputation: 12684

You can get the week number of day then get the max value per week number.

select t1.*
from table1 t1
join (
select week(day) as wknum, 
       max(day) as day
from   table1 
group by week(day)
     ) t2
on t1.day=t2.day


Result:
day         value
2018-03-16  62
2018-03-21  102
2018-03-29  132

Upvotes: 3

Daniel Marcus
Daniel Marcus

Reputation: 2686

Here's how you would do it in SQL Server - Use mysql equivalent

select b.day, b.value from (
select datepart(ww,day) a, max(day) b
from yourtable
group by datepart(ww,day))a
join yourtable b on a.a=datepart(ww,b.day) and a.b=b.day

Upvotes: 1

dfundako
dfundako

Reputation: 8314

This solution uses window functions and picks the latest date within the week.

https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

I use SQL Server, but I believe this is the MySQL equivalent:

with cte AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY WEEKOFYEAR([day]) ORDER BY DAYOFWEEK([day]) DESC) AS counter_
from #table1 
)

SELECT [day], [value] 
FROM cte 
WHERE counter_ = 1

Upvotes: 1

Related Questions