Aggregate max date on variable group by

dagon

an evolution of this post: http://www.phpbuilder.com/board/showthread.php?t=10369853

Code:

id |money|date

1 1 2009-12-01 00:00:00
1_1 5 2009-11-01 00:00:00
1_2 7 2009-12-01 00:00:00
1_3 6 2009-4-01 00:00:00
2 3 2009-12-01 00:00:00
3 5 2009-12-01 00:00:00
4 8 2009-12-01 00:00:00
4_1 9 2009-8-01 00:00:00

i need the most recent 'money' where id has underscore, so result from above should be

1 7
4 9

Bonus point for combining with previous query.😃

any suggestions welcome

dagon

answer:

--wrong 🙁

Lars_Berg

[code]
select id,
       money,
       (select sum(money)
          from t
         where substring(id from 1 for position('_' in id)-1) = 
               substring(q.id from 1 for position('_' in q.id)-1)) as totalMoney
  from t as q
 where date in
    (select max(date)
        from t
     where id like '&#37;!_%' escape '!')
  and id like '%!_%' escape '!'