mysql grouping problem

Question

Hello folks,

I have the following table:

date1              date2       sc   cash    date
"2010-09-20"    "2010-09-21"    202 300 "2010-03-01"
"2010-09-20"    "2010-09-21"    202 600 "2010-08-01"
"2010-09-20"    "2010-09-21"    202 670 "2010-08-20"
"2010-09-20"    "2010-09-21"    202 710 "2010-09-01"
"2010-09-20"    "2010-09-21"    202 870 "2010-09-21"
"2010-09-21"    "2010-09-22"    199 300 "2010-03-01"
"2010-09-21"    "2010-09-22"    199 600 "2010-08-01"
"2010-09-21"    "2010-09-22"    199 670 "2010-08-20"
"2010-09-21"    "2010-09-22"    199 710 "2010-09-01"
"2010-09-21"    "2010-09-22"    199 870 "2010-09-21"

What is to group by (date1,date2) and (cash,date) such that date = max(date <= date1)

date1              date2       sc   cash    date
"2010-09-20"    "2010-09-21"    202 870 "2010-09-21"
"2010-09-21"    "2010-09-22"    199 870 "2010-09-21"

Answer 1

what you need is to use the HAVING clause... i'll just assume that you have an id on each row.. (if you don't, you'll have to modify my query, but it'll be harder =D )

SELECT date1,date2,sc,cash,date
FROM mytable 
WHERE id IN (SELECT id
             FROM mytable GROUP BY date1,date2
             HAVING max(date)>=date1)

i've tested it in Postgresql and works fine.Sorry, I haven't been able to test it in MySql

Good Luck