VFP9 SQL quest: GROUP BY return more rows, need only one 1

[Eliott]

Greetings, I tried to solve this task by using Enginebehavior=90 in VFP9 but by using Group clause I got more rows but I need only one, with most recent date for that year. In this table I wish to extract records with "status" for specific time, based on date. I thought, it's easy, I'll ask for all fields from table and date I'll compare with maximum date for some particular year, if in some year is more dates. But, not so easy. Here is table:

empcode  wyear     wdate     ht  status   position   worktime
Emp001   2011  2011/10/20  1  Work     Leader     Shift1
Emp001   2011  2011/10/15  1  Work     Assist     Shift1
Emp001   2011  2011/05/10  2  Work     Assist     Shift2
Emp002   2011  2011/08/22  2  Work     Leader     Shift1
Emp002   2011  2011/10/21  1  Work     Assist     Shift1
Emp003   2011  2011/06/23  2  Standb   Assist     Shift1
Emp004   2011  2011/01/20  1  Standb   Leader     Shift1
Emp005   2008  2008/02/10  2  Work     Leader     Shift2
Emp005   2010  2010/11/23  1  Work     Assist     Shift2

SELECT empcode, status, position, worktime, ht from TimeTable WHERE wdate in (SELECT MAX(wdate) FROM TimeTable WHERE wyear="2011" GROUP BY empcode) 
 GROUP BY empcode,status,position,worktime,ht ORDER BY empcode

but instead to get for employer emp001 record with wdate 2011/10/20 and rest of fileds from this record I got another record for wdate 2011/10/15, probably due to difference in field mentioned behind GROUP BY! How to avoid this error and get a record just with max wdate for particular year, like this?

Emp001   2011  2011/10/20  1  Work     Leader     Shift1
Emp002   2011  2011/10/21  1  Work     Assist     Shift1
Emp003   2011  2011/06/23  2  Standb   Assist     Shift1
Emp004   2011  2011/01/20  1  Standb   Leader     Shift1...

Thank you.

There is no good nor evil, just decisions and consequences.

 

[TamarGranor]

If I understand your question correctly, what you want is the most recent record for each value of EmpCode. You need to use a correlated subquery, so you only look at results for the particular empcode.

SELECT empcode, status, position, worktime, ht ;
  FROM TimeTable ;
  WHERE wDate = ;
    (SELECT MAX(wdate) AS MaxDate ;
       FROM TimeTable Sub ;
       WHERE Sub.wyear="2011" ;
         AND Sub.EmpCode = TimeTable.EmpCode) 
  ORDER BY empcode

Tamar

 

[Eliott]

Thank you Tamar, based on how your code looks & feel, I think it could be acceptable solution. In meantime I tried to solve issue with Group by split SQL into two pass: first one make cursor with max date and belonging employee, second one is some kind of inner join. But I must admit your solution is faster and has clean and simple code.
Thanks. Such tricky Set enginebahvior to 70 and 90 drive me crazy as sometime test data have not all possible situation and then produce weird situation and "kinder surprise" query results
Thank you again man.

There is no good nor evil, just decisions and consequences.

 

[TamarGranor]

Unless you must support legacy code that contains a lot of queries, your best bet is to keep ENGINEBEHAVIOR set to 90. 70 allows queries that have wrong results.

Tamar