core reorg
[csql.git] / test / sql / Aggregate / exp.testnw033.ksh
blob11166186959712b7d6535ff9455eca2e62994edc
1 Network CSql
2 Statement Executed
3 Statement Executed: Rows Affected = 1
4 Statement Executed: Rows Affected = 1
5 Statement Executed: Rows Affected = 1
6 Statement Executed: Rows Affected = 1
7 Statement Executed: Rows Affected = 1
8 Statement Executed: Rows Affected = 1
9 Statement Executed: Rows Affected = 1
10 Statement Executed: Rows Affected = 1
11 Statement Executed: Rows Affected = 1
12 Statement Executed: Rows Affected = 1
13 Statement Executed: Rows Affected = 1
14 Statement Executed: Rows Affected = 1
15 Statement Executed: Rows Affected = 1
16 Statement Executed: Rows Affected = 1
17 Statement Executed: Rows Affected = 1
18 Statement Executed: Rows Affected = 1
19 echo select * from t1;
20 ---------------------------------------------------------
21 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5 t1.f6 t1.f7 t1.f8 t1.f9
22 ---------------------------------------------------------
23 10 100 1000 Bijay 1000.000000 100000.000000 2009/3/1 11:59:59.0 2009/3/1 11:59:59.0
24 11 110 1100 Jitendr 2000.000000 200000.000000 2009/3/2 11:59:58.0 2009/3/2 11:59:59.0
25 12 120 1000 Nihar 3000.000000 300000.000000 2009/3/3 11:59:57.0 2009/3/3 11:59:59.0
26 13 130 NULL Kishor 4000.000000 400000.000000 2009/3/4 11:59:59.0 2009/3/4 11:59:59.0
27 14 100 1000 Praba 5000.000000 500000.000000 2009/3/1 11:59:58.0 2009/3/1 11:59:59.0
28 15 110 1100 Sanjit 6000.000000 600000.000000 2009/3/2 11:59:57.0 2009/3/2 11:59:59.0
29 16 120 1000 Sanjay NULL 700000.000000 2009/3/3 11:59:59.0 2009/3/3 11:59:59.0
30 17 130 1100 Arindam 8000.000000 800000.000000 2009/3/4 11:59:58.0 2009/3/4 11:59:59.0
31 10 100 1000 Bijay 9000.000000 900000.000000 2009/3/1 11:59:57.0 2009/3/1 11:59:59.0
32 11 NULL 1100 Jitendr 1000.000000 100000.000000 2009/3/2 11:59:58.0 2009/3/2 11:59:59.0
33 12 120 1000 Nihar 2000.000000 200000.000000 2009/3/3 NULL 2009/3/3 11:59:59.0
34 13 130 1100 Kishor 3000.000000 300000.000000 2009/3/5 11:59:56.0 2009/3/5 11:59:59.0
35 14 100 1000 Praba 4000.000000 400000.000000 2009/3/1 11:59:55.0 2009/3/4 11:59:59.0
36 15 110 NULL Sanjit 5000.000000 500000.000000 2009/3/2 11:59:56.0 2009/3/1 11:59:59.0
37 16 120 1000 Sanjay 6000.000000 600000.000000 2009/3/3 11:59:55.0 2009/3/3 11:59:59.0
38 17 130 1100 Arindam 7000.000000 700000.000000 2009/3/1 11:59:59.0 2009/3/1 11:59:59.0
40 Network CSql
41 echo select f1,f2,min(f4),min(f7),min(f8),min(f9) from t1 group by f1,f2;
42 ---------------------------------------------------------
43 f1 f2 MIN(f4) MIN(f7) MIN(f8) MIN(f9)
44 ---------------------------------------------------------
45 10 100 Bijay 2009/3/1 11:59:57.0 2009/3/1 11:59:59.0
46 11 110 Jitendr 2009/3/2 11:59:58.0 2009/3/2 11:59:59.0
47 12 120 Nihar 2009/3/3 11:59:57.0 2009/3/3 11:59:59.0
48 13 130 Kishor 2009/3/4 11:59:56.0 2009/3/4 11:59:59.0
49 14 100 Praba 2009/3/1 11:59:55.0 2009/3/1 11:59:59.0
50 15 110 Sanjit 2009/3/2 11:59:56.0 2009/3/1 11:59:59.0
51 16 120 Sanjay 2009/3/3 11:59:55.0 2009/3/3 11:59:59.0
52 17 130 Arindam 2009/3/1 11:59:58.0 2009/3/1 11:59:59.0
53 11 NULL Jitendr 2009/3/2 11:59:58.0 2009/3/2 11:59:59.0
55 echo select f1,f2,min(f4),min(f7),min(f8),min(f9) from t1 group by f1,f2 having min(f4)>='K';
56 ---------------------------------------------------------
57 f1 f2 MIN(f4) MIN(f7) MIN(f8) MIN(f9)
58 ---------------------------------------------------------
59 12 120 Nihar 2009/3/3 11:59:57.0 2009/3/3 11:59:59.0
60 13 130 Kishor 2009/3/4 11:59:56.0 2009/3/4 11:59:59.0
61 14 100 Praba 2009/3/1 11:59:55.0 2009/3/1 11:59:59.0
62 15 110 Sanjit 2009/3/2 11:59:56.0 2009/3/1 11:59:59.0
63 16 120 Sanjay 2009/3/3 11:59:55.0 2009/3/3 11:59:59.0
65 echo select f1,f2,min(f4),min(f7),min(f8),min(f9) from t1 where f1 >= 12 group by f1,f2;
66 ---------------------------------------------------------
67 f1 f2 MIN(f4) MIN(f7) MIN(f8) MIN(f9)
68 ---------------------------------------------------------
69 12 120 Nihar 2009/3/3 11:59:57.0 2009/3/3 11:59:59.0
70 13 130 Kishor 2009/3/4 11:59:56.0 2009/3/4 11:59:59.0
71 14 100 Praba 2009/3/1 11:59:55.0 2009/3/1 11:59:59.0
72 15 110 Sanjit 2009/3/2 11:59:56.0 2009/3/1 11:59:59.0
73 16 120 Sanjay 2009/3/3 11:59:55.0 2009/3/3 11:59:59.0
74 17 130 Arindam 2009/3/1 11:59:58.0 2009/3/1 11:59:59.0
76 echo select f1,f2,min(f4),min(f7),min(f8),min(f9) from t1 where f1 >= 12 group by f1,f2 having min(f8)>='11:59:55';
77 ---------------------------------------------------------
78 f1 f2 MIN(f4) MIN(f7) MIN(f8) MIN(f9)
79 ---------------------------------------------------------
80 12 120 Nihar 2009/3/3 11:59:57.0 2009/3/3 11:59:59.0
81 13 130 Kishor 2009/3/4 11:59:56.0 2009/3/4 11:59:59.0
82 14 100 Praba 2009/3/1 11:59:55.0 2009/3/1 11:59:59.0
83 15 110 Sanjit 2009/3/2 11:59:56.0 2009/3/1 11:59:59.0
84 16 120 Sanjay 2009/3/3 11:59:55.0 2009/3/3 11:59:59.0
85 17 130 Arindam 2009/3/1 11:59:58.0 2009/3/1 11:59:59.0
87 echo select f1,f2,min(f4),min(f7),min(f8),min(f9) from t1 where f1 >= 12 and f1<15 group by f1,f2;
88 ---------------------------------------------------------
89 f1 f2 MIN(f4) MIN(f7) MIN(f8) MIN(f9)
90 ---------------------------------------------------------
91 12 120 Nihar 2009/3/3 11:59:57.0 2009/3/3 11:59:59.0
92 13 130 Kishor 2009/3/4 11:59:56.0 2009/3/4 11:59:59.0
93 14 100 Praba 2009/3/1 11:59:55.0 2009/3/1 11:59:59.0
95 echo select f1,f2,min(f4),min(f7),min(f8),min(f9) from t1 where f1 >= 12 and f1<15 group by f1,f2 having min(f9)>='2009-03-03 11:59:59';
96 ---------------------------------------------------------
97 f1 f2 MIN(f4) MIN(f7) MIN(f8) MIN(f9)
98 ---------------------------------------------------------
99 12 120 Nihar 2009/3/3 11:59:57.0 2009/3/3 11:59:59.0
100 13 130 Kishor 2009/3/4 11:59:56.0 2009/3/4 11:59:59.0
102 echo select f1,f2,min(f4),min(f7),min(f8),min(f9) from t1 where f1 >= 12 and f1<15 group by f1,f2 having min(f4)>='K' and min(f8) <'11:59:56';
103 ---------------------------------------------------------
104 f1 f2 MIN(f4) MIN(f7) MIN(f8) MIN(f9)
105 ---------------------------------------------------------
106 14 100 Praba 2009/3/1 11:59:55.0 2009/3/1 11:59:59.0
108 echo select f1,f2,min(f4),min(f7),min(f8),min(f9) from t1 where f1 >= 12 and f2<130 group by f1,f2 ;
109 ---------------------------------------------------------
110 f1 f2 MIN(f4) MIN(f7) MIN(f8) MIN(f9)
111 ---------------------------------------------------------
112 12 120 Nihar 2009/3/3 11:59:57.0 2009/3/3 11:59:59.0
113 14 100 Praba 2009/3/1 11:59:55.0 2009/3/1 11:59:59.0
114 15 110 Sanjit 2009/3/2 11:59:56.0 2009/3/1 11:59:59.0
115 16 120 Sanjay 2009/3/3 11:59:55.0 2009/3/3 11:59:59.0
117 echo select f1,f2,min(f4),min(f7),min(f8),min(f9) from t1 where f1 >= 12 and f2<130 group by f1,f2 having min(f7)>='2009/3/2' and min(f9)<>'2009-03-01 11:59:59';
118 ---------------------------------------------------------
119 f1 f2 MIN(f4) MIN(f7) MIN(f8) MIN(f9)
120 ---------------------------------------------------------
121 12 120 Nihar 2009/3/3 11:59:57.0 2009/3/3 11:59:59.0
122 16 120 Sanjay 2009/3/3 11:59:55.0 2009/3/3 11:59:59.0
124 echo select f7,f8,f9,min(f4) from t1 group by f7,f8,f9;
125 ---------------------------------------------------------
126 f7 f8 f9 MIN(f4)
127 ---------------------------------------------------------
128 2009/3/1 11:59:59.0 2009/3/1 11:59:59.0 Arindam
129 2009/3/2 11:59:58.0 2009/3/2 11:59:59.0 Jitendr
130 2009/3/3 11:59:57.0 2009/3/3 11:59:59.0 Nihar
131 2009/3/4 11:59:59.0 2009/3/4 11:59:59.0 Kishor
132 2009/3/1 11:59:58.0 2009/3/1 11:59:59.0 Praba
133 2009/3/2 11:59:57.0 2009/3/2 11:59:59.0 Sanjit
134 2009/3/3 11:59:59.0 2009/3/3 11:59:59.0 Sanjay
135 2009/3/4 11:59:58.0 2009/3/4 11:59:59.0 Arindam
136 2009/3/1 11:59:57.0 2009/3/1 11:59:59.0 Bijay
137 2009/3/3 NULL 2009/3/3 11:59:59.0 Nihar
138 2009/3/5 11:59:56.0 2009/3/5 11:59:59.0 Kishor
139 2009/3/1 11:59:55.0 2009/3/4 11:59:59.0 Praba
140 2009/3/2 11:59:56.0 2009/3/1 11:59:59.0 Sanjit
141 2009/3/3 11:59:55.0 2009/3/3 11:59:59.0 Sanjay
143 echo select f9,f7,f8,min(f4) from t1 group by f7,f8,f9;
144 ---------------------------------------------------------
145 f9 f7 f8 MIN(f4)
146 ---------------------------------------------------------
147 2009/3/1 11:59:59.0 2009/3/1 11:59:59.0 Arindam
148 2009/3/2 11:59:59.0 2009/3/2 11:59:58.0 Jitendr
149 2009/3/3 11:59:59.0 2009/3/3 11:59:57.0 Nihar
150 2009/3/4 11:59:59.0 2009/3/4 11:59:59.0 Kishor
151 2009/3/1 11:59:59.0 2009/3/1 11:59:58.0 Praba
152 2009/3/2 11:59:59.0 2009/3/2 11:59:57.0 Sanjit
153 2009/3/3 11:59:59.0 2009/3/3 11:59:59.0 Sanjay
154 2009/3/4 11:59:59.0 2009/3/4 11:59:58.0 Arindam
155 2009/3/1 11:59:59.0 2009/3/1 11:59:57.0 Bijay
156 2009/3/3 11:59:59.0 2009/3/3 NULL Nihar
157 2009/3/5 11:59:59.0 2009/3/5 11:59:56.0 Kishor
158 2009/3/4 11:59:59.0 2009/3/1 11:59:55.0 Praba
159 2009/3/1 11:59:59.0 2009/3/2 11:59:56.0 Sanjit
160 2009/3/3 11:59:59.0 2009/3/3 11:59:55.0 Sanjay
162 echo select f7,f8,f9,min(f4) from t1 group by f7,f9,f8;
163 ---------------------------------------------------------
164 f7 f8 f9 MIN(f4)
165 ---------------------------------------------------------
166 2009/3/1 11:59:59.0 2009/3/1 11:59:59.0 Arindam
167 2009/3/2 11:59:58.0 2009/3/2 11:59:59.0 Jitendr
168 2009/3/3 11:59:57.0 2009/3/3 11:59:59.0 Nihar
169 2009/3/4 11:59:59.0 2009/3/4 11:59:59.0 Kishor
170 2009/3/1 11:59:58.0 2009/3/1 11:59:59.0 Praba
171 2009/3/2 11:59:57.0 2009/3/2 11:59:59.0 Sanjit
172 2009/3/3 11:59:59.0 2009/3/3 11:59:59.0 Sanjay
173 2009/3/4 11:59:58.0 2009/3/4 11:59:59.0 Arindam
174 2009/3/1 11:59:57.0 2009/3/1 11:59:59.0 Bijay
175 2009/3/3 NULL 2009/3/3 11:59:59.0 Nihar
176 2009/3/5 11:59:56.0 2009/3/5 11:59:59.0 Kishor
177 2009/3/1 11:59:55.0 2009/3/4 11:59:59.0 Praba
178 2009/3/2 11:59:56.0 2009/3/1 11:59:59.0 Sanjit
179 2009/3/3 11:59:55.0 2009/3/3 11:59:59.0 Sanjay
181 echo select f7,f8,f9,min(f4) from t1 group by f7,f9,f8 having min(f4)>='K' and min(f4) <'Q';
182 ---------------------------------------------------------
183 f7 f8 f9 MIN(f4)
184 ---------------------------------------------------------
185 2009/3/3 11:59:57.0 2009/3/3 11:59:59.0 Nihar
186 2009/3/4 11:59:59.0 2009/3/4 11:59:59.0 Kishor
187 2009/3/1 11:59:58.0 2009/3/1 11:59:59.0 Praba
188 2009/3/3 NULL 2009/3/3 11:59:59.0 Nihar
189 2009/3/5 11:59:56.0 2009/3/5 11:59:59.0 Kishor
190 2009/3/1 11:59:55.0 2009/3/4 11:59:59.0 Praba
192 echo select f4,f8,f9,min(f7) from t1 group by f4,f8,f9;
193 ---------------------------------------------------------
194 f4 f8 f9 MIN(f7)
195 ---------------------------------------------------------
196 Bijay 11:59:59.0 2009/3/1 11:59:59.0 2009/3/1
197 Jitendr 11:59:58.0 2009/3/2 11:59:59.0 2009/3/2
198 Nihar 11:59:57.0 2009/3/3 11:59:59.0 2009/3/3
199 Kishor 11:59:59.0 2009/3/4 11:59:59.0 2009/3/4
200 Praba 11:59:58.0 2009/3/1 11:59:59.0 2009/3/1
201 Sanjit 11:59:57.0 2009/3/2 11:59:59.0 2009/3/2
202 Sanjay 11:59:59.0 2009/3/3 11:59:59.0 2009/3/3
203 Arindam 11:59:58.0 2009/3/4 11:59:59.0 2009/3/4
204 Bijay 11:59:57.0 2009/3/1 11:59:59.0 2009/3/1
205 Nihar NULL 2009/3/3 11:59:59.0 2009/3/3
206 Kishor 11:59:56.0 2009/3/5 11:59:59.0 2009/3/5
207 Praba 11:59:55.0 2009/3/4 11:59:59.0 2009/3/1
208 Sanjit 11:59:56.0 2009/3/1 11:59:59.0 2009/3/2
209 Sanjay 11:59:55.0 2009/3/3 11:59:59.0 2009/3/3
210 Arindam 11:59:59.0 2009/3/1 11:59:59.0 2009/3/1
212 echo select f4,f8,f9,min(f7) from t1 group by f4,f8,f9 having min(f7)<='2009-03-04' and min(f7) >'2009-03-02' ;
213 ---------------------------------------------------------
214 f4 f8 f9 MIN(f7)
215 ---------------------------------------------------------
216 Nihar 11:59:57.0 2009/3/3 11:59:59.0 2009/3/3
217 Kishor 11:59:59.0 2009/3/4 11:59:59.0 2009/3/4
218 Sanjay 11:59:59.0 2009/3/3 11:59:59.0 2009/3/3
219 Arindam 11:59:58.0 2009/3/4 11:59:59.0 2009/3/4
220 Nihar NULL 2009/3/3 11:59:59.0 2009/3/3
221 Sanjay 11:59:55.0 2009/3/3 11:59:59.0 2009/3/3
223 echo select f9,f7,f4,min(f8) from t1 group by f4,f7,f9;
224 ---------------------------------------------------------
225 f9 f7 f4 MIN(f8)
226 ---------------------------------------------------------
227 2009/3/1 11:59:59.0 2009/3/1 Bijay 11:59:57.0
228 2009/3/2 11:59:59.0 2009/3/2 Jitendr 11:59:58.0
229 2009/3/3 11:59:59.0 2009/3/3 Nihar 11:59:57.0
230 2009/3/4 11:59:59.0 2009/3/4 Kishor 11:59:59.0
231 2009/3/1 11:59:59.0 2009/3/1 Praba 11:59:58.0
232 2009/3/2 11:59:59.0 2009/3/2 Sanjit 11:59:57.0
233 2009/3/3 11:59:59.0 2009/3/3 Sanjay 11:59:55.0
234 2009/3/4 11:59:59.0 2009/3/4 Arindam 11:59:58.0
235 2009/3/5 11:59:59.0 2009/3/5 Kishor 11:59:56.0
236 2009/3/4 11:59:59.0 2009/3/1 Praba 11:59:55.0
237 2009/3/1 11:59:59.0 2009/3/2 Sanjit 11:59:56.0
238 2009/3/1 11:59:59.0 2009/3/1 Arindam 11:59:59.0
240 echo select f9,f7,f4,min(f8) from t1 group by f4,f7,f9 having min(f8)<='11:59:59' and min(f8) >'11:59:57' ;
241 ---------------------------------------------------------
242 f9 f7 f4 MIN(f8)
243 ---------------------------------------------------------
244 2009/3/2 11:59:59.0 2009/3/2 Jitendr 11:59:58.0
245 2009/3/4 11:59:59.0 2009/3/4 Kishor 11:59:59.0
246 2009/3/1 11:59:59.0 2009/3/1 Praba 11:59:58.0
247 2009/3/4 11:59:59.0 2009/3/4 Arindam 11:59:58.0
248 2009/3/1 11:59:59.0 2009/3/1 Arindam 11:59:59.0
250 echo select f7,f8,f4,min(f9) from t1 group by f4,f7,f8;
251 ---------------------------------------------------------
252 f7 f8 f4 MIN(f9)
253 ---------------------------------------------------------
254 2009/3/1 11:59:59.0 Bijay 2009/3/1 11:59:59.0
255 2009/3/2 11:59:58.0 Jitendr 2009/3/2 11:59:59.0
256 2009/3/3 11:59:57.0 Nihar 2009/3/3 11:59:59.0
257 2009/3/4 11:59:59.0 Kishor 2009/3/4 11:59:59.0
258 2009/3/1 11:59:58.0 Praba 2009/3/1 11:59:59.0
259 2009/3/2 11:59:57.0 Sanjit 2009/3/2 11:59:59.0
260 2009/3/3 11:59:59.0 Sanjay 2009/3/3 11:59:59.0
261 2009/3/4 11:59:58.0 Arindam 2009/3/4 11:59:59.0
262 2009/3/1 11:59:57.0 Bijay 2009/3/1 11:59:59.0
263 2009/3/3 NULL Nihar 2009/3/3 11:59:59.0
264 2009/3/5 11:59:56.0 Kishor 2009/3/5 11:59:59.0
265 2009/3/1 11:59:55.0 Praba 2009/3/4 11:59:59.0
266 2009/3/2 11:59:56.0 Sanjit 2009/3/1 11:59:59.0
267 2009/3/3 11:59:55.0 Sanjay 2009/3/3 11:59:59.0
268 2009/3/1 11:59:59.0 Arindam 2009/3/1 11:59:59.0
270 echo select f7,f8,f4,min(f9) from t1 group by f4,f7,f8 having min(f9)<='2009-03-05 11:59:59' and min(f9) >'2009-03-02 11:59:59' ;
271 ---------------------------------------------------------
272 f7 f8 f4 MIN(f9)
273 ---------------------------------------------------------
274 2009/3/3 11:59:57.0 Nihar 2009/3/3 11:59:59.0
275 2009/3/4 11:59:59.0 Kishor 2009/3/4 11:59:59.0
276 2009/3/3 11:59:59.0 Sanjay 2009/3/3 11:59:59.0
277 2009/3/4 11:59:58.0 Arindam 2009/3/4 11:59:59.0
278 2009/3/3 NULL Nihar 2009/3/3 11:59:59.0
279 2009/3/5 11:59:56.0 Kishor 2009/3/5 11:59:59.0
280 2009/3/1 11:59:55.0 Praba 2009/3/4 11:59:59.0
281 2009/3/3 11:59:55.0 Sanjay 2009/3/3 11:59:59.0
283 Statement Executed