test/sql/Aggregate/exp.test034.ksh

   1 Statement Executed
   2 Statement Executed: Rows Affected = 1
   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 echo select * from t1;
  19 ---------------------------------------------------------
  20         t1.f1   t1.f2   t1.f3   t1.f4   t1.f5   t1.f6   t1.f7   t1.f8   t1.f9
  21 ---------------------------------------------------------
  22         10      100     1000    Bijay   1000.000000     100000.000000   2009/3/1        11:59:59.0      2009/3/1 11:59:59.0
  23         11      110     1100    Jitendr 2000.000000     200000.000000   2009/3/2        11:59:58.0      2009/3/2 11:59:59.0
  24         12      120     1000    Nihar   3000.000000     300000.000000   2009/3/3        11:59:57.0      2009/3/3 11:59:59.0
  25         13      130     NULL    Kishor  4000.000000     400000.000000   2009/3/4        11:59:59.0      2009/3/4 11:59:59.0
  26         14      100     1000    Praba   5000.000000     500000.000000   2009/3/1        11:59:58.0      2009/3/1 11:59:59.0
  27         15      110     1100    Sanjit  6000.000000     600000.000000   2009/3/2        11:59:57.0      2009/3/2 11:59:59.0
  28         16      120     1000    Sanjay  NULL    700000.000000   2009/3/3        11:59:59.0      2009/3/3 11:59:59.0
  29         17      130     1100    Arindam 8000.000000     800000.000000   2009/3/4        11:59:58.0      2009/3/4 11:59:59.0
  30         10      100     1000    Bijay   9000.000000     900000.000000   2009/3/1        11:59:57.0      2009/3/1 11:59:59.0
  31         11      NULL    1100    Jitendr 1000.000000     100000.000000   2009/3/2        11:59:58.0      2009/3/2 11:59:59.0
  32         12      120     1000    Nihar   2000.000000     200000.000000   2009/3/3        NULL    2009/3/3 11:59:59.0
  33         13      130     1100    Kishor  3000.000000     300000.000000   2009/3/5        11:59:56.0      2009/3/5 11:59:59.0
  34         14      100     1000    Praba   4000.000000     400000.000000   2009/3/1        11:59:55.0      2009/3/4 11:59:59.0
  35         15      110     NULL    Sanjit  5000.000000     500000.000000   2009/3/2        11:59:56.0      2009/3/1 11:59:59.0
  36         16      120     1000    Sanjay  6000.000000     600000.000000   2009/3/3        11:59:55.0      2009/3/3 11:59:59.0
  37         17      130     1100    Arindam 7000.000000     700000.000000   2009/3/1        11:59:59.0      2009/3/1 11:59:59.0
  38
  39 echo select f1,f2,max(f4),max(f7),max(f8),max(f9)  from t1 group by f1,f2;
  40 ---------------------------------------------------------
  41         f1      f2      MAX(f4) MAX(f7) MAX(f8) MAX(f9)
  42 ---------------------------------------------------------
  43         10      100     Bijay   2009/3/1        11:59:59.0      2009/3/1 11:59:59.0
  44         11      110     Jitendr 2009/3/2        11:59:58.0      2009/3/2 11:59:59.0
  45         12      120     Nihar   2009/3/3        11:59:57.0      2009/3/3 11:59:59.0
  46         13      130     Kishor  2009/3/5        11:59:59.0      2009/3/5 11:59:59.0
  47         14      100     Praba   2009/3/1        11:59:58.0      2009/3/4 11:59:59.0
  48         15      110     Sanjit  2009/3/2        11:59:57.0      2009/3/2 11:59:59.0
  49         16      120     Sanjay  2009/3/3        11:59:59.0      2009/3/3 11:59:59.0
  50         17      130     Arindam 2009/3/4        11:59:59.0      2009/3/4 11:59:59.0
  51         11      NULL    Jitendr 2009/3/2        11:59:58.0      2009/3/2 11:59:59.0
  52
  53 echo select f1,f2,max(f4),max(f7),max(f8),max(f9)  from t1 group by f1,f2 having max(f4)>='K';
  54 ---------------------------------------------------------
  55         f1      f2      MAX(f4) MAX(f7) MAX(f8) MAX(f9)
  56 ---------------------------------------------------------
  57         12      120     Nihar   2009/3/3        11:59:57.0      2009/3/3 11:59:59.0
  58         13      130     Kishor  2009/3/5        11:59:59.0      2009/3/5 11:59:59.0
  59         14      100     Praba   2009/3/1        11:59:58.0      2009/3/4 11:59:59.0
  60         15      110     Sanjit  2009/3/2        11:59:57.0      2009/3/2 11:59:59.0
  61         16      120     Sanjay  2009/3/3        11:59:59.0      2009/3/3 11:59:59.0
  62
  63 echo select f1,f2,max(f4),max(f7),max(f8),max(f9)  from t1 where f1 >= 12 group by f1,f2;
  64 ---------------------------------------------------------
  65         f1      f2      MAX(f4) MAX(f7) MAX(f8) MAX(f9)
  66 ---------------------------------------------------------
  67         12      120     Nihar   2009/3/3        11:59:57.0      2009/3/3 11:59:59.0
  68         13      130     Kishor  2009/3/5        11:59:59.0      2009/3/5 11:59:59.0
  69         14      100     Praba   2009/3/1        11:59:58.0      2009/3/4 11:59:59.0
  70         15      110     Sanjit  2009/3/2        11:59:57.0      2009/3/2 11:59:59.0
  71         16      120     Sanjay  2009/3/3        11:59:59.0      2009/3/3 11:59:59.0
  72         17      130     Arindam 2009/3/4        11:59:59.0      2009/3/4 11:59:59.0
  73
  74 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';
  75 ---------------------------------------------------------
  76         f1      f2      MAX(f4) MAX(f7) MAX(f8) MAX(f9)
  77 ---------------------------------------------------------
  78         12      120     Nihar   2009/3/3        11:59:57.0      2009/3/3 11:59:59.0
  79         13      130     Kishor  2009/3/5        11:59:59.0      2009/3/5 11:59:59.0
  80         14      100     Praba   2009/3/1        11:59:58.0      2009/3/4 11:59:59.0
  81         15      110     Sanjit  2009/3/2        11:59:57.0      2009/3/2 11:59:59.0
  82         16      120     Sanjay  2009/3/3        11:59:59.0      2009/3/3 11:59:59.0
  83         17      130     Arindam 2009/3/4        11:59:59.0      2009/3/4 11:59:59.0
  84
  85 echo select f1,f2,max(f4),max(f7),max(f8),max(f9)  from t1 where f1 >= 12 and f1<15 group by f1,f2;
  86 ---------------------------------------------------------
  87         f1      f2      MAX(f4) MAX(f7) MAX(f8) MAX(f9)
  88 ---------------------------------------------------------
  89         12      120     Nihar   2009/3/3        11:59:57.0      2009/3/3 11:59:59.0
  90         13      130     Kishor  2009/3/5        11:59:59.0      2009/3/5 11:59:59.0
  91         14      100     Praba   2009/3/1        11:59:58.0      2009/3/4 11:59:59.0
  92
  93 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';
  94 ---------------------------------------------------------
  95         f1      f2      MAX(f4) MAX(f7) MAX(f8) MAX(f9)
  96 ---------------------------------------------------------
  97         12      120     Nihar   2009/3/3        11:59:57.0      2009/3/3 11:59:59.0
  98         13      130     Kishor  2009/3/5        11:59:59.0      2009/3/5 11:59:59.0
  99         14      100     Praba   2009/3/1        11:59:58.0      2009/3/4 11:59:59.0
 100
 101 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';
 102 ---------------------------------------------------------
 103         f1      f2      MAX(f4) MAX(f7) MAX(f8) MAX(f9)
 104 ---------------------------------------------------------
 105         12      120     Nihar   2009/3/3        11:59:57.0      2009/3/3 11:59:59.0
 106
 107 echo select f1,f2,max(f4),max(f7),max(f8),max(f9)  from t1 where f1 >= 12 and f2<130 group by f1,f2 ;
 108 ---------------------------------------------------------
 109         f1      f2      MAX(f4) MAX(f7) MAX(f8) MAX(f9)
 110 ---------------------------------------------------------
 111         12      120     Nihar   2009/3/3        11:59:57.0      2009/3/3 11:59:59.0
 112         14      100     Praba   2009/3/1        11:59:58.0      2009/3/4 11:59:59.0
 113         15      110     Sanjit  2009/3/2        11:59:57.0      2009/3/2 11:59:59.0
 114         16      120     Sanjay  2009/3/3        11:59:59.0      2009/3/3 11:59:59.0
 115
 116 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';
 117 ---------------------------------------------------------
 118         f1      f2      MAX(f4) MAX(f7) MAX(f8) MAX(f9)
 119 ---------------------------------------------------------
 120         12      120     Nihar   2009/3/3        11:59:57.0      2009/3/3 11:59:59.0
 121         15      110     Sanjit  2009/3/2        11:59:57.0      2009/3/2 11:59:59.0
 122         16      120     Sanjay  2009/3/3        11:59:59.0      2009/3/3 11:59:59.0
 123
 124 echo select f7,f8,f9,max(f4) from t1 group by f7,f8,f9;
 125 ---------------------------------------------------------
 126         f7      f8      f9      MAX(f4)
 127 ---------------------------------------------------------
 128         2009/3/1        11:59:59.0      2009/3/1 11:59:59.0     Bijay
 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
 142
 143 echo select f9,f7,f8,max(f4) from t1 group by f7,f8,f9;
 144 ---------------------------------------------------------
 145         f9      f7      f8      MAX(f4)
 146 ---------------------------------------------------------
 147         2009/3/1 11:59:59.0     2009/3/1        11:59:59.0      Bijay
 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
 161
 162 echo select f7,f8,f9,max(f4) from t1 group by f7,f9,f8;
 163 ---------------------------------------------------------
 164         f7      f8      f9      MAX(f4)
 165 ---------------------------------------------------------
 166         2009/3/1        11:59:59.0      2009/3/1 11:59:59.0     Bijay
 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
 180
 181 echo select f7,f8,f9,max(f4) from t1 group by f7,f9,f8 having max(f4)>='K' and max(f4) <'Q';
 182 ---------------------------------------------------------
 183         f7      f8      f9      MAX(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
 191
 192 echo select f4,f8,f9,max(f7) from t1 group by f4,f8,f9;
 193 ---------------------------------------------------------
 194         f4      f8      f9      MAX(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
 211
 212 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' ;
 213 ---------------------------------------------------------
 214         f4      f8      f9      MAX(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
 222
 223 echo select f9,f7,f4,max(f8) from t1 group by f4,f7,f9;
 224 ---------------------------------------------------------
 225         f9      f7      f4      MAX(f8)
 226 ---------------------------------------------------------
 227         2009/3/1 11:59:59.0     2009/3/1        Bijay   11:59:59.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:59.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
 239
 240 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' ;
 241 ---------------------------------------------------------
 242         f9      f7      f4      MAX(f8)
 243 ---------------------------------------------------------
 244         2009/3/1 11:59:59.0     2009/3/1        Bijay   11:59:59.0
 245         2009/3/2 11:59:59.0     2009/3/2        Jitendr 11:59:58.0
 246         2009/3/4 11:59:59.0     2009/3/4        Kishor  11:59:59.0
 247         2009/3/1 11:59:59.0     2009/3/1        Praba   11:59:58.0
 248         2009/3/3 11:59:59.0     2009/3/3        Sanjay  11:59:59.0
 249         2009/3/4 11:59:59.0     2009/3/4        Arindam 11:59:58.0
 250         2009/3/1 11:59:59.0     2009/3/1        Arindam 11:59:59.0
 251
 252 echo select f7,f8,f4,max(f9) from t1 group by f4,f7,f8;
 253 ---------------------------------------------------------
 254         f7      f8      f4      MAX(f9)
 255 ---------------------------------------------------------
 256         2009/3/1        11:59:59.0      Bijay   2009/3/1 11:59:59.0
 257         2009/3/2        11:59:58.0      Jitendr 2009/3/2 11:59:59.0
 258         2009/3/3        11:59:57.0      Nihar   2009/3/3 11:59:59.0
 259         2009/3/4        11:59:59.0      Kishor  2009/3/4 11:59:59.0
 260         2009/3/1        11:59:58.0      Praba   2009/3/1 11:59:59.0
 261         2009/3/2        11:59:57.0      Sanjit  2009/3/2 11:59:59.0
 262         2009/3/3        11:59:59.0      Sanjay  2009/3/3 11:59:59.0
 263         2009/3/4        11:59:58.0      Arindam 2009/3/4 11:59:59.0
 264         2009/3/1        11:59:57.0      Bijay   2009/3/1 11:59:59.0
 265         2009/3/3        NULL    Nihar   2009/3/3 11:59:59.0
 266         2009/3/5        11:59:56.0      Kishor  2009/3/5 11:59:59.0
 267         2009/3/1        11:59:55.0      Praba   2009/3/4 11:59:59.0
 268         2009/3/2        11:59:56.0      Sanjit  2009/3/1 11:59:59.0
 269         2009/3/3        11:59:55.0      Sanjay  2009/3/3 11:59:59.0
 270         2009/3/1        11:59:59.0      Arindam 2009/3/1 11:59:59.0
 271
 272 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' ;
 273 ---------------------------------------------------------
 274         f7      f8      f4      MAX(f9)
 275 ---------------------------------------------------------
 276         2009/3/3        11:59:57.0      Nihar   2009/3/3 11:59:59.0
 277         2009/3/4        11:59:59.0      Kishor  2009/3/4 11:59:59.0
 278         2009/3/3        11:59:59.0      Sanjay  2009/3/3 11:59:59.0
 279         2009/3/4        11:59:58.0      Arindam 2009/3/4 11:59:59.0
 280         2009/3/3        NULL    Nihar   2009/3/3 11:59:59.0
 281         2009/3/5        11:59:56.0      Kishor  2009/3/5 11:59:59.0
 282         2009/3/1        11:59:55.0      Praba   2009/3/4 11:59:59.0
 283         2009/3/3        11:59:55.0      Sanjay  2009/3/3 11:59:59.0
 284
 285 Statement Executed