CODEWITHSUNDEEP
phpmysql
Donatas K.
Donatas K.

Reputation: 866

First and last records from group by. Cannot get last

I have a table

Date        value1  value2
2012-09-07  1       1
2012-09-06  2       2
2012-09-05  3       3
2012-09-04  4       4
2012-09-03  5       5
2012-08-31  6       6
2012-08-30  7       7
2012-08-29  8       8
2012-08-28  9       9
2012-08-27  10      10
2012-08-24  11      11
2012-08-23  12      12
2012-08-22  13      13

values in the table is not ascending like in example. There are random numbers.

I need to get the date of the week start, value1 on the beginning of the week and value2 at the end of the week.

Date field is unique, and it's stores day dates only so no duplicate dates are allowed.

I tried to use the query below:

SELECT MIN(`Date`) as Date, 
    (SELECT `value1` ORDER BY `Date` ASC LIMIT 1) as Start, 
    (SELECT `value2` ORDER BY `Date` DESC LIMIT 1) as End
FROM table
GROUP BY YEAR(`Date`), WEEK(`Date`,7)

The query returns grouped weeks and value1 correctly but value2 is also from the row of the week start i.e.

2012-08-27  10      10

but I need:

2012-08-27  10      6

What do I do wrong?

Upvotes: 0

Views: 137

Answers (1)

Kris
Kris

Reputation: 6122

How about something like this

SELECT `date`, value1 as Start, 
(SELECT value2 FROM photos WHERE t.date >= adddate(`Date`, INTERVAL 1-DAYOFWEEK(`Date`) DAY) AND  t.date <= adddate(`Date`, INTERVAL 7-DAYOFWEEK(`Date`) DAY) ORDER BY date DESC LIMIT 1) as endDate
from table t 
GROUP BY YEAR(`Date`), WEEK(`Date`,7)

There may be a more optimal way to do it.. but this works

Upvotes: 1

Related Questions