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