group by - SQL query help with GROUPing and nested queries -

given 2 tables,

• table a containing customerid, lastchange, internallink

• table b containing internallink, turnover

(i'm simplyfying here , changing generic example, actual structure more complex. sql dialect mysql.)

the unique thing (per table) internallink. there several records in same customerid, different dates in lastchange, , different internallink values. there other items linked this; cannot change tables.

i need recordids recent customer (highest lastchange value of same customerid) and entries in b match value condition linked.

i think

select `internallink` `b` (`turnover` > 10000)  

part not issue.

i got far:

select `customerid`, max(`lastchange`)   `a`   `lastchange` in (select `internallink` `b`                           `turnover` > 10000)  group `customerid`; 

alas, statement gives wrong results, because above return me customerids recent value not fulfill criteria, older did - selects oldest did, , returns this. in case recent entry below threshold, customerid should not turn @ all.

where did go wrong, , correct approach this?

sample data table a

 customerid   lastchange   internallink          3   2010-02-11   11          3   2010-09-04   12          3   2010-10-22   13          3   2010-11-23   14          4   2010-05-05   15          4   2010-12-01   16          5   2010-11-28   17          5   2010-11-29   18 

table b

 internallink  turnover           11     47000           12     11000           13      8000           14     15000           15     17000           16     23000           17     50000           18     10000 

the actual threshold in tests 12000. can see customerid should not in result set, since recent entry below threshold.

the result set should (3,2010-11-23)(4,2010-12-01) - contains (5,2010-11-28), wrong.

---

getting bit closer (with help, thanks!), these 2 statements both work:

select customerid, max(lastchange), internallink group customerid; select internallink b (turnover > 12000);

now need intersection of both... correct logic!

the following query should want. not performant way write kind of query. it's using standard sql , executes in database.

the works this: inner subquery finds customerids along latest changedate. each such pair (customerid, lastchange), find original row in table a. having found row in table a, use internallink find matching record in b, if associated turnover greater 10000.

drop table a; drop table b;  create table a(    customerid   int  not null   ,lastchange   date not null   ,internallink int  not null );  create table b(    internallink int not null   ,turnover     int not null );  insert values(3, date '2010-02-11', 11); insert values(3, date '2010-09-04', 12); insert values(3, date '2010-10-22', 13); insert values(3, date '2010-11-23', 14); insert values(4, date '2010-05-05', 15); insert values(4, date '2010-12-01', 16); insert values(5, date '2010-11-28', 17); insert values(5, date '2010-11-29', 18);  insert b values(11, 47000); insert b values(12, 11000); insert b values(13,  8000); insert b values(14, 15000); insert b values(15, 17000); insert b values(16, 23000); insert b values(17, 50000); insert b values(18, 10000);  select a.customerid       ,a.lastchange       ,a.internallink       ,b.turnover     join b on (a.internallink = b.internallink)  b.turnover > 10000    , (a.customerid, a.lastchange) in(select customerid,max(lastchange)                                                                               group customerid);