>I have been trying to pickup the maximum(date_visit) i.e.. the last record in table2 or Record No. 11 in table. By
>givin the below SQl Command its giving erroneous results.
>
>select distinct max(tran_trans.date_visit) as last_visit,tran_trans.mac_type,tran_trans.serial_no, ;
> tran_trans.location,;
> tran_trans.t1_cntr,tran_trans.t2_cntr_act,tran_trans.t2_cntr_rst,;
> tran_trans.t3_cntr_act,tran_trans.t3_cntr_rst,ct_act,ct_rst,eng_visit,due_date,status,;
> action,spare_parts,sir_no from data\tran_trans;
> where mac_type='BMW-01' and serial_no='122121';
> order by last_visit,mac_type,serial_no,location,t1_cntr;
> group by tran_trans.mac_type,tran_trans.serial_no,;
> tran_trans.location;
> into cursor query1
>
>
>Please Help as to where I am growing wrong.
>
>
>Regards
>Ravi
It seems to me that you want to pick-up record with max(date_visit) for each record group (mac_type+serial_no+location). If it's really so, then you problem is that MAX() will really retrieve maximum date value for each group, but any other fields will be just the last (or even random) record from each group.
You should provide that records will be in physical order: Select * from tran_trans Into cursor tmp nofilter order by mac_type,serial_no,location,date_visit
Now you can group it:
Select * from tmp Into cursor query1 group by mac_type,serial_no,location
Edward Pikman
Independent Consultant