=================
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
