================= Without the group by clause the result set is: sqlite> select awal1.Code, awal1.Level, awal1.Category, awal1.Product, awal1.Location, awal1."Begin",akhir1."End", strftime("%s",akh ir1."End")-strftime("%s",awal1."Begin") as Difference from awal1, akhir1 where awal1.Code = akhir1.Code and akhir1.Category like awa l1.Category || '%' and awal1.Product = akhir1.Product and awal1.Location = akhir1.Location and akhir1."End" >= awal1."Begin"; A1220 SMALL FOOD MARGARINE HOMS 1 2007-05-06 11:42:46 2007-05-06 11:42:46 0 A1221 SMALL FOOD CAKE HOMS 2 2007-05-06 11:31:57 2007-05-06 11:31:57 0 A1221 SMALL FOOD CAKE HOMS 1 2007-05-06 11:31:57 2007-05-06 11:31:57 0 A1221 SMALL FOOD CAKE HOMS 1 2007-05-06 11:31:57 2007-05-06 11:42:46 649 A1221 SMALL FOOD CAKE HOMS 1 2007-05-06 11:42:46 2007-05-06 11:42:46 0 A1222 SMALL FOOD WAFER HOMS 2 2007-05-06 11:20:34 2007-05-06 11:31:57 683 A1222 SMALL FOOD WAFER HOMS 1 2007-05-06 11:20:34 2007-05-06 11:31:57 683 A1222 SMALL FOOD WAFER HOMS 1 2007-05-06 11:20:34 2007-05-06 11:42:46 1332 A1222 SMALL FOOD WAFER HOMS 1 2007-05-06 11:42:46 2007-05-06 11:42:46 0 A1236 MEDIUM FOOD SNACK HOMS 2 2007-05-06 10:48:57 2007-05-06 11:19:21 1824 A1236 MEDIUM FOOD SNACK HOMS 1 2007-05-06 10:48:57 2007-05-06 11:19:25 1828 A1269 SMALL CLOTHES BELT HOMS 3 2007-05-07 17:28:25 2007-05-07 17:28:27 2
The group by clause combines rows A1221 SMALL FOOD CAKE HOMS 1 2007-05-06 11:31:57 2007-05-06 11:31:57 0 A1221 SMALL FOOD CAKE HOMS 1 2007-05-06 11:31:57 2007-05-06 11:42:46 649 into 1 row. The values in the columns not included in the group by clause ("Begin", "End" and Difference) could be from any of the combined rows (which rows is not, I believe, specified in any standard). MySql and Sqlite seem to result in different selections. If you want specific rows, then you need to modify the query to control the data selection. In this case it appears that: select awal1.Code, awal1.Level, awal1.Category, awal1.Product, awal1.Location, awal1."Begin", min( akhir1."End" ), min( strftime("%s",akhir1."End")-strftime("%s",awal1."Begin") ) as Difference from awal1, akhir1 where awal1.Code = akhir1.Code and akhir1.Category like awal1.Category || '%' and awal1.Product = akhir1.Product and awal1.Location = akhir1.Location and akhir1."End" >= awal1."Begin" group by awal1."Begin", awal1.Code, awal1.Category, awal1.Product, awal1.Location; gives the result you want. Rgds, Simon ================= thanks to Simon. it works!!!!. but i have some question. i hope u can help me to explain these : 1. is it necessary or not to specify min(awal1.begin). 2. is "min(strftime("%s",akhir1."End")-strftime("%s",awal1."Begin")) as Difference" is more time consuming to compute than "strftime("%s",min(akhir1."End"))-strftime("%s",awal1."Begin") as Difference". i think these give the same result. 3. akhir1.Category like awal1.Category || '%'. what does it mean? why is use || '%'???. why not to write "awal1.category = akhir1.category". it also give the same result. once again, i would say thank you for ur solution. sory for my bad english. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users