You can copy the below message onto a notepad the list below will become significant


Table 1
MAC_TYPE LEFT(serial_no,6) LOCATION T1_CNTR T2_CNTR_ACT T2_CNTR_RST T3_CNTR_ACT T3_CNTR_RST CT_ACT CT_RST LEFT(eng_visit,2) DATE_VISIT DUE_DATE
1 BMW-01 122121 AEDXB 200 2000 2000 200 232 32 23 NN 10/10/97 10/10/97
2 BMW-01 122121 AEDXB 200 2000 2000 200 2000 23 23 NN 10/10/97 10/10/97
3 BMW-01 122121 AEDXB 200 320 232 32 32 32 32 NN 10/10/97 10/10/97
4 BMW-01 122121 AEDXB 200 232 3232 3232 323 32323 3232 NN 10/10/97 10/10/97
5 BMW-01 122121 AEDXB 200 32323 3232 2332 3232 32323 3232 NN 10/10/97 10/10/97
6 BMW-01 122121 AEDXB 200 0 0 0 0 0 0 NN 10/10/97 10/10/97
7 BMW-01 122121 AEDXB 200 202002 2020 2020 2020 2020 202 NN 10/02/98 10/03/98
8 BMW-01 122121 AEDXB 7000 8000 9000 2500 32200 34500 2500 NN 12/10/98 18/12/98
9 BMW-01 122121 AEDXB 950 2020 2020 330 3030 2400 2500 NN 10/10/98 10/10/98
10 BMW-01 122121 AEDXB 10000 1000 95000 95000 30000 40404 3003 NN 10/10/98 12/12/98
11 BMW-01 122121 AEDXB 4500 23232 3232 2232 1212 1212 12121 NN 12/12/98 13/12/98
12 BMW-01 122121 AEDXB 3500 23232 12121 1212 121 12121 12121 NN 10/10/98 11/10/98
13 BMW-01 122121 AEDXB 5000 4500 49494 23232 2323 3232 3232 NN 10/10/98 11/12/98
14 BMW-01 122121 AEDXB 9500 9050 90901 9090 90 9090 909 NN 10/10/98 18/08/98
15 BMW-01 122121 AEDXB 10000 10000 101010 101010 101001 1010 1010 NN / / 21/12/21
16 BMW-01 122121 AEDXB 90900 9090 900 9090 990 9090 0 NN 10/10/98 11/10/98
17 BMW-01 122121 AEDXB 8900 89001 19818 19919 19191 191919 1121 NN / / / /
18 BMW-01 122121 AEDXB 432 32094 32293 3232832 3293829 3293829 3298329 NN 06/07/98 06/07/98
19 BMW-01 122121 AEDXB 3232 2121 2121 23121 2121 2121 2121 NN 06/07/98 06/07/98
20 BMW-01 122121 AEDXB 32321 1001 10101 101010 10101 101010 10101 NN 06/07/98 06/07/98



Table 2

Above table Indexed using Date Visit

MAC_TYPE LEFT(serial_no,6) LOCATION T1_CNTR T2_CNTR_ACT T2_CNTR_RST T3_CNTR_ACT T3_CNTR_RST CT_ACT CT_RST LEFT(eng_visit,2) DATE_VISIT DUE_DATE
1 BMW-01 122121 AEDXB 10000 10000 101010 101010 101001 1010 1010 NN / / 21/12/21
2 BMW-01 122121 AEDXB 8900 89001 19818 19919 19191 191919 1121 NN / / / /
3 BMW-01 122121 AEDXB 432 32094 32293 3232832 3293829 3293829 3298329 NN 06/07/98 06/07/98
4 BMW-01 122121 AEDXB 3232 2121 2121 23121 2121 2121 2121 NN 06/07/98 06/07/98
5 BMW-01 122121 AEDXB 32321 1001 10101 101010 10101 101010 10101 NN 06/07/98 06/07/98
6 BMW-01 122121 AEDXB 200 202002 2020 2020 2020 2020 202 NN 10/02/98 10/03/98
7 BMW-01 122121 AEDXB 200 2000 2000 200 232 32 23 NN 10/10/97 10/10/97
8 BMW-01 122121 AEDXB 200 2000 2000 200 2000 23 23 NN 10/10/97 10/10/97
9 BMW-01 122121 AEDXB 200 320 232 32 32 32 32 NN 10/10/97 10/10/97
10 BMW-01 122121 AEDXB 200 232 3232 3232 323 32323 3232 NN 10/10/97 10/10/97
11 BMW-01 122121 AEDXB 200 32323 3232 2332 3232 32323 3232 NN 10/10/97 10/10/97
12 BMW-01 122121 AEDXB 200 0 0 0 0 0 0 NN 10/10/97 10/10/97
13 BMW-01 122121 AEDXB 950 2020 2020 330 3030 2400 2500 NN 10/10/98 10/10/98
14 BMW-01 122121 AEDXB 10000 1000 95000 95000 30000 40404 3003 NN 10/10/98 12/12/98
15 BMW-01 122121 AEDXB 3500 23232 12121 1212 121 12121 12121 NN 10/10/98 11/10/98
16 BMW-01 122121 AEDXB 5000 4500 49494 23232 2323 3232 3232 NN 10/10/98 11/12/98
17 BMW-01 122121 AEDXB 9500 9050 90901 9090 90 9090 909 NN 10/10/98 18/08/98
18 BMW-01 122121 AEDXB 90900 9090 900 9090 990 9090 0 NN 10/10/98 11/10/98
19 BMW-01 122121 AEDXB 7000 8000 9000 2500 32200 34500 2500 NN 12/10/98 18/12/98
20 BMW-01 122121 AEDXB 4500 23232 3232 2232 1212 1212 12121 NN 12/12/98 13/12/98


Table1 & Table 2 are same. Table 1 No Indexes, Table 2 indexed on Date_visit

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