test/sql/Aggregate/exp.testnw034.ksh

   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
  39
  40 Network CSql
  41 echo select f1,f2,max(f4),max(f7),max(f8),max(f9)  from t1 group by f1,f2;
  42 ---------------------------------------------------------
  43         f1      f2      MAX(f4) MAX(f7) MAX(f8) MAX(f9)
  44 ---------------------------------------------------------
  45         10      100     Bijay   2009/3/1        11:59:59.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/5        11:59:59.0      2009/3/5 11:59:59.0
  49         14      100     Praba   2009/3/1        11:59:58.0      2009/3/4 11:59:59.0
  50         15      110     Sanjit  2009/3/2        11:59:57.0      2009/3/2 11:59:59.0
  51         16      120     Sanjay  2009/3/3        11:59:59.0      2009/3/3 11:59:59.0
  52         17      130     Arindam 2009/3/4        11:59:59.0      2009/3/4 11:59:59.0
  53         11      NULL    Jitendr 2009/3/2        11:59:58.0      2009/3/2 11:59:59.0
  54
  55 echo select f1,f2,max(f4),max(f7),max(f8),max(f9)  from t1 group by f1,f2 having max(f4)>='K';
  56 ---------------------------------------------------------
  57         f1      f2      MAX(f4) MAX(f7) MAX(f8) MAX(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/5        11:59:59.0      2009/3/5 11:59:59.0
  61         14      100     Praba   2009/3/1        11:59:58.0      2009/3/4 11:59:59.0
  62         15      110     Sanjit  2009/3/2        11:59:57.0      2009/3/2 11:59:59.0
  63         16      120     Sanjay  2009/3/3        11:59:59.0      2009/3/3 11:59:59.0
  64
  65 echo select f1,f2,max(f4),max(f7),max(f8),max(f9)  from t1 where f1 >= 12 group by f1,f2;
  66 ---------------------------------------------------------
  67         f1      f2      MAX(f4) MAX(f7) MAX(f8) MAX(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/5        11:59:59.0      2009/3/5 11:59:59.0
  71         14      100     Praba   2009/3/1        11:59:58.0      2009/3/4 11:59:59.0
  72         15      110     Sanjit  2009/3/2        11:59:57.0      2009/3/2 11:59:59.0
  73         16      120     Sanjay  2009/3/3        11:59:59.0      2009/3/3 11:59:59.0
  74         17      130     Arindam 2009/3/4        11:59:59.0      2009/3/4 11:59:59.0
  75
  76 echo select f1,f2,max(f4),max(f7),max(f8),max(f9)  from t1 where f1 >= 12 group by f1,f2 having max(f8)>='11:59:55';
  77 ---------------------------------------------------------
  78         f1      f2      MAX(f4) MAX(f7) MAX(f8) MAX(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/5        11:59:59.0      2009/3/5 11:59:59.0
  82         14      100     Praba   2009/3/1        11:59:58.0      2009/3/4 11:59:59.0
  83         15      110     Sanjit  2009/3/2        11:59:57.0      2009/3/2 11:59:59.0
  84         16      120     Sanjay  2009/3/3        11:59:59.0      2009/3/3 11:59:59.0
  85         17      130     Arindam 2009/3/4        11:59:59.0      2009/3/4 11:59:59.0
  86
  87 echo select f1,f2,max(f4),max(f7),max(f8),max(f9)  from t1 where f1 >= 12 and f1<15 group by f1,f2;
  88 ---------------------------------------------------------
  89         f1      f2      MAX(f4) MAX(f7) MAX(f8) MAX(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/5        11:59:59.0      2009/3/5 11:59:59.0
  93         14      100     Praba   2009/3/1        11:59:58.0      2009/3/4 11:59:59.0
  94
  95 echo select f1,f2,max(f4),max(f7),max(f8),max(f9)  from t1 where f1 >= 12 and f1<15 group by f1,f2 having max(f9)>='2009-03-03 11:59:59';
  96 ---------------------------------------------------------
  97         f1      f2      MAX(f4) MAX(f7) MAX(f8) MAX(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/5        11:59:59.0      2009/3/5 11:59:59.0
 101         14      100     Praba   2009/3/1        11:59:58.0      2009/3/4 11:59:59.0
 102
 103 echo select f1,f2,max(f4),max(f7),max(f8),max(f9)  from t1 where f1 >= 12 and f1<15 group by f1,f2 having max(f4)>='K' and max(f8) <'11:59:58';
 104 ---------------------------------------------------------
 105         f1      f2      MAX(f4) MAX(f7) MAX(f8) MAX(f9)
 106 ---------------------------------------------------------
 107         12      120     Nihar   2009/3/3        11:59:57.0      2009/3/3 11:59:59.0
 108
 109 echo select f1,f2,max(f4),max(f7),max(f8),max(f9)  from t1 where f1 >= 12 and f2<130 group by f1,f2 ;
 110 ---------------------------------------------------------
 111         f1      f2      MAX(f4) MAX(f7) MAX(f8) MAX(f9)
 112 ---------------------------------------------------------
 113         12      120     Nihar   2009/3/3        11:59:57.0      2009/3/3 11:59:59.0
 114         14      100     Praba   2009/3/1        11:59:58.0      2009/3/4 11:59:59.0
 115         15      110     Sanjit  2009/3/2        11:59:57.0      2009/3/2 11:59:59.0
 116         16      120     Sanjay  2009/3/3        11:59:59.0      2009/3/3 11:59:59.0
 117
 118 echo select f1,f2,max(f4),max(f7),max(f8),max(f9)  from t1 where f1 >= 12 and f2<130 group by f1,f2 having max(f7)>='2009/3/2' and max(f9)<>'2009-03-01 11:59:59';
 119 ---------------------------------------------------------
 120         f1      f2      MAX(f4) MAX(f7) MAX(f8) MAX(f9)
 121 ---------------------------------------------------------
 122         12      120     Nihar   2009/3/3        11:59:57.0      2009/3/3 11:59:59.0
 123         15      110     Sanjit  2009/3/2        11:59:57.0      2009/3/2 11:59:59.0
 124         16      120     Sanjay  2009/3/3        11:59:59.0      2009/3/3 11:59:59.0
 125
 126 echo select f7,f8,f9,max(f4) from t1 group by f7,f8,f9;
 127 ---------------------------------------------------------
 128         f7      f8      f9      MAX(f4)
 129 ---------------------------------------------------------
 130         2009/3/1        11:59:59.0      2009/3/1 11:59:59.0     Bijay
 131         2009/3/2        11:59:58.0      2009/3/2 11:59:59.0     Jitendr
 132         2009/3/3        11:59:57.0      2009/3/3 11:59:59.0     Nihar
 133         2009/3/4        11:59:59.0      2009/3/4 11:59:59.0     Kishor
 134         2009/3/1        11:59:58.0      2009/3/1 11:59:59.0     Praba
 135         2009/3/2        11:59:57.0      2009/3/2 11:59:59.0     Sanjit
 136         2009/3/3        11:59:59.0      2009/3/3 11:59:59.0     Sanjay
 137         2009/3/4        11:59:58.0      2009/3/4 11:59:59.0     Arindam
 138         2009/3/1        11:59:57.0      2009/3/1 11:59:59.0     Bijay
 139         2009/3/3        NULL    2009/3/3 11:59:59.0     Nihar
 140         2009/3/5        11:59:56.0      2009/3/5 11:59:59.0     Kishor
 141         2009/3/1        11:59:55.0      2009/3/4 11:59:59.0     Praba
 142         2009/3/2        11:59:56.0      2009/3/1 11:59:59.0     Sanjit
 143         2009/3/3        11:59:55.0      2009/3/3 11:59:59.0     Sanjay
 144
 145 echo select f9,f7,f8,max(f4) from t1 group by f7,f8,f9;
 146 ---------------------------------------------------------
 147         f9      f7      f8      MAX(f4)
 148 ---------------------------------------------------------
 149         2009/3/1 11:59:59.0     2009/3/1        11:59:59.0      Bijay
 150         2009/3/2 11:59:59.0     2009/3/2        11:59:58.0      Jitendr
 151         2009/3/3 11:59:59.0     2009/3/3        11:59:57.0      Nihar
 152         2009/3/4 11:59:59.0     2009/3/4        11:59:59.0      Kishor
 153         2009/3/1 11:59:59.0     2009/3/1        11:59:58.0      Praba
 154         2009/3/2 11:59:59.0     2009/3/2        11:59:57.0      Sanjit
 155         2009/3/3 11:59:59.0     2009/3/3        11:59:59.0      Sanjay
 156         2009/3/4 11:59:59.0     2009/3/4        11:59:58.0      Arindam
 157         2009/3/1 11:59:59.0     2009/3/1        11:59:57.0      Bijay
 158         2009/3/3 11:59:59.0     2009/3/3        NULL    Nihar
 159         2009/3/5 11:59:59.0     2009/3/5        11:59:56.0      Kishor
 160         2009/3/4 11:59:59.0     2009/3/1        11:59:55.0      Praba
 161         2009/3/1 11:59:59.0     2009/3/2        11:59:56.0      Sanjit
 162         2009/3/3 11:59:59.0     2009/3/3        11:59:55.0      Sanjay
 163
 164 echo select f7,f8,f9,max(f4) from t1 group by f7,f9,f8;
 165 ---------------------------------------------------------
 166         f7      f8      f9      MAX(f4)
 167 ---------------------------------------------------------
 168         2009/3/1        11:59:59.0      2009/3/1 11:59:59.0     Bijay
 169         2009/3/2        11:59:58.0      2009/3/2 11:59:59.0     Jitendr
 170         2009/3/3        11:59:57.0      2009/3/3 11:59:59.0     Nihar
 171         2009/3/4        11:59:59.0      2009/3/4 11:59:59.0     Kishor
 172         2009/3/1        11:59:58.0      2009/3/1 11:59:59.0     Praba
 173         2009/3/2        11:59:57.0      2009/3/2 11:59:59.0     Sanjit
 174         2009/3/3        11:59:59.0      2009/3/3 11:59:59.0     Sanjay
 175         2009/3/4        11:59:58.0      2009/3/4 11:59:59.0     Arindam
 176         2009/3/1        11:59:57.0      2009/3/1 11:59:59.0     Bijay
 177         2009/3/3        NULL    2009/3/3 11:59:59.0     Nihar
 178         2009/3/5        11:59:56.0      2009/3/5 11:59:59.0     Kishor
 179         2009/3/1        11:59:55.0      2009/3/4 11:59:59.0     Praba
 180         2009/3/2        11:59:56.0      2009/3/1 11:59:59.0     Sanjit
 181         2009/3/3        11:59:55.0      2009/3/3 11:59:59.0     Sanjay
 182
 183 echo select f7,f8,f9,max(f4) from t1 group by f7,f9,f8 having max(f4)>='K' and max(f4) <'Q';
 184 ---------------------------------------------------------
 185         f7      f8      f9      MAX(f4)
 186 ---------------------------------------------------------
 187         2009/3/3        11:59:57.0      2009/3/3 11:59:59.0     Nihar
 188         2009/3/4        11:59:59.0      2009/3/4 11:59:59.0     Kishor
 189         2009/3/1        11:59:58.0      2009/3/1 11:59:59.0     Praba
 190         2009/3/3        NULL    2009/3/3 11:59:59.0     Nihar
 191         2009/3/5        11:59:56.0      2009/3/5 11:59:59.0     Kishor
 192         2009/3/1        11:59:55.0      2009/3/4 11:59:59.0     Praba
 193
 194 echo select f4,f8,f9,max(f7) from t1 group by f4,f8,f9;
 195 ---------------------------------------------------------
 196         f4      f8      f9      MAX(f7)
 197 ---------------------------------------------------------
 198         Bijay   11:59:59.0      2009/3/1 11:59:59.0     2009/3/1
 199         Jitendr 11:59:58.0      2009/3/2 11:59:59.0     2009/3/2
 200         Nihar   11:59:57.0      2009/3/3 11:59:59.0     2009/3/3
 201         Kishor  11:59:59.0      2009/3/4 11:59:59.0     2009/3/4
 202         Praba   11:59:58.0      2009/3/1 11:59:59.0     2009/3/1
 203         Sanjit  11:59:57.0      2009/3/2 11:59:59.0     2009/3/2
 204         Sanjay  11:59:59.0      2009/3/3 11:59:59.0     2009/3/3
 205         Arindam 11:59:58.0      2009/3/4 11:59:59.0     2009/3/4
 206         Bijay   11:59:57.0      2009/3/1 11:59:59.0     2009/3/1
 207         Nihar   NULL    2009/3/3 11:59:59.0     2009/3/3
 208         Kishor  11:59:56.0      2009/3/5 11:59:59.0     2009/3/5
 209         Praba   11:59:55.0      2009/3/4 11:59:59.0     2009/3/1
 210         Sanjit  11:59:56.0      2009/3/1 11:59:59.0     2009/3/2
 211         Sanjay  11:59:55.0      2009/3/3 11:59:59.0     2009/3/3
 212         Arindam 11:59:59.0      2009/3/1 11:59:59.0     2009/3/1
 213
 214 echo select f4,f8,f9,max(f7) from t1 group by f4,f8,f9 having max(f7)<='2009-03-04' and max(f7) >'2009-03-02' ;
 215 ---------------------------------------------------------
 216         f4      f8      f9      MAX(f7)
 217 ---------------------------------------------------------
 218         Nihar   11:59:57.0      2009/3/3 11:59:59.0     2009/3/3
 219         Kishor  11:59:59.0      2009/3/4 11:59:59.0     2009/3/4
 220         Sanjay  11:59:59.0      2009/3/3 11:59:59.0     2009/3/3
 221         Arindam 11:59:58.0      2009/3/4 11:59:59.0     2009/3/4
 222         Nihar   NULL    2009/3/3 11:59:59.0     2009/3/3
 223         Sanjay  11:59:55.0      2009/3/3 11:59:59.0     2009/3/3
 224
 225 echo select f9,f7,f4,max(f8) from t1 group by f4,f7,f9;
 226 ---------------------------------------------------------
 227         f9      f7      f4      MAX(f8)
 228 ---------------------------------------------------------
 229         2009/3/1 11:59:59.0     2009/3/1        Bijay   11:59:59.0
 230         2009/3/2 11:59:59.0     2009/3/2        Jitendr 11:59:58.0
 231         2009/3/3 11:59:59.0     2009/3/3        Nihar   11:59:57.0
 232         2009/3/4 11:59:59.0     2009/3/4        Kishor  11:59:59.0
 233         2009/3/1 11:59:59.0     2009/3/1        Praba   11:59:58.0
 234         2009/3/2 11:59:59.0     2009/3/2        Sanjit  11:59:57.0
 235         2009/3/3 11:59:59.0     2009/3/3        Sanjay  11:59:59.0
 236         2009/3/4 11:59:59.0     2009/3/4        Arindam 11:59:58.0
 237         2009/3/5 11:59:59.0     2009/3/5        Kishor  11:59:56.0
 238         2009/3/4 11:59:59.0     2009/3/1        Praba   11:59:55.0
 239         2009/3/1 11:59:59.0     2009/3/2        Sanjit  11:59:56.0
 240         2009/3/1 11:59:59.0     2009/3/1        Arindam 11:59:59.0
 241
 242 echo select f9,f7,f4,max(f8) from t1 group by f4,f7,f9 having max(f8)<='11:59:59' and max(f8) >'11:59:57' ;
 243 ---------------------------------------------------------
 244         f9      f7      f4      MAX(f8)
 245 ---------------------------------------------------------
 246         2009/3/1 11:59:59.0     2009/3/1        Bijay   11:59:59.0
 247         2009/3/2 11:59:59.0     2009/3/2        Jitendr 11:59:58.0
 248         2009/3/4 11:59:59.0     2009/3/4        Kishor  11:59:59.0
 249         2009/3/1 11:59:59.0     2009/3/1        Praba   11:59:58.0
 250         2009/3/3 11:59:59.0     2009/3/3        Sanjay  11:59:59.0
 251         2009/3/4 11:59:59.0     2009/3/4        Arindam 11:59:58.0
 252         2009/3/1 11:59:59.0     2009/3/1        Arindam 11:59:59.0
 253
 254 echo select f7,f8,f4,max(f9) from t1 group by f4,f7,f8;
 255 ---------------------------------------------------------
 256         f7      f8      f4      MAX(f9)
 257 ---------------------------------------------------------
 258         2009/3/1        11:59:59.0      Bijay   2009/3/1 11:59:59.0
 259         2009/3/2        11:59:58.0      Jitendr 2009/3/2 11:59:59.0
 260         2009/3/3        11:59:57.0      Nihar   2009/3/3 11:59:59.0
 261         2009/3/4        11:59:59.0      Kishor  2009/3/4 11:59:59.0
 262         2009/3/1        11:59:58.0      Praba   2009/3/1 11:59:59.0
 263         2009/3/2        11:59:57.0      Sanjit  2009/3/2 11:59:59.0
 264         2009/3/3        11:59:59.0      Sanjay  2009/3/3 11:59:59.0
 265         2009/3/4        11:59:58.0      Arindam 2009/3/4 11:59:59.0
 266         2009/3/1        11:59:57.0      Bijay   2009/3/1 11:59:59.0
 267         2009/3/3        NULL    Nihar   2009/3/3 11:59:59.0
 268         2009/3/5        11:59:56.0      Kishor  2009/3/5 11:59:59.0
 269         2009/3/1        11:59:55.0      Praba   2009/3/4 11:59:59.0
 270         2009/3/2        11:59:56.0      Sanjit  2009/3/1 11:59:59.0
 271         2009/3/3        11:59:55.0      Sanjay  2009/3/3 11:59:59.0
 272         2009/3/1        11:59:59.0      Arindam 2009/3/1 11:59:59.0
 273
 274 echo select f7,f8,f4,max(f9) from t1 group by f4,f7,f8 having max(f9)<='2009-03-05 11:59:59' and max(f9) >'2009-03-02 11:59:59' ;
 275 ---------------------------------------------------------
 276         f7      f8      f4      MAX(f9)
 277 ---------------------------------------------------------
 278         2009/3/3        11:59:57.0      Nihar   2009/3/3 11:59:59.0
 279         2009/3/4        11:59:59.0      Kishor  2009/3/4 11:59:59.0
 280         2009/3/3        11:59:59.0      Sanjay  2009/3/3 11:59:59.0
 281         2009/3/4        11:59:58.0      Arindam 2009/3/4 11:59:59.0
 282         2009/3/3        NULL    Nihar   2009/3/3 11:59:59.0
 283         2009/3/5        11:59:56.0      Kishor  2009/3/5 11:59:59.0
 284         2009/3/1        11:59:55.0      Praba   2009/3/4 11:59:59.0
 285         2009/3/3        11:59:55.0      Sanjay  2009/3/3 11:59:59.0
 286
 287 Statement Executed