test/sql/Limit/exp.test003.ksh

   1 echo CREATE TABLE t1(f1 int, f2 char(10), f3 date);
   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 echo SELECT * FROM t1;
  19 ---------------------------------------------------------
  20         t1.f1   t1.f2   t1.f3
  21 ---------------------------------------------------------
  22         1       ABCD    2009/8/1
  23         3       BCDA    2009/8/2
  24         5       CDBA    2009/8/3
  25         9       DCBA    2009/8/9
  26         7       ACBD    2009/8/7
  27         2       PQRS    2009/8/2
  28         3       TUVW    2009/8/3
  29         4       EFGH    2009/8/4
  30         5       IJKL    2009/8/7
  31         6       MNOP    2009/8/8
  32         2       MMMM    2009/8/2
  33         3       NNNN    2009/8/3
  34         4       OOOO    2009/8/4
  35         5       PPPP    2009/8/7
  36         6       QQQQ    2009/8/8
  37
  38 echo SELECT * FROM t1 ORDER BY f1;
  39 ---------------------------------------------------------
  40         t1.f1   t1.f2   t1.f3
  41 ---------------------------------------------------------
  42         1       ABCD    2009/8/1
  43         2       MMMM    2009/8/2
  44         2       PQRS    2009/8/2
  45         3       NNNN    2009/8/3
  46         3       TUVW    2009/8/3
  47         3       BCDA    2009/8/2
  48         4       OOOO    2009/8/4
  49         4       EFGH    2009/8/4
  50         5       PPPP    2009/8/7
  51         5       IJKL    2009/8/7
  52         5       CDBA    2009/8/3
  53         6       QQQQ    2009/8/8
  54         6       MNOP    2009/8/8
  55         7       ACBD    2009/8/7
  56         9       DCBA    2009/8/9
  57
  58 echo SELECT * FROM t1 ORDER BY f1 LIMIT 5;
  59 ---------------------------------------------------------
  60         t1.f1   t1.f2   t1.f3
  61 ---------------------------------------------------------
  62         1       ABCD    2009/8/1
  63         2       MMMM    2009/8/2
  64         2       PQRS    2009/8/2
  65         3       NNNN    2009/8/3
  66         3       TUVW    2009/8/3
  67
  68 echo SELECT * FROM t1 ORDER BY f1 LIMIT 5 OFFSET 5;
  69 ---------------------------------------------------------
  70         t1.f1   t1.f2   t1.f3
  71 ---------------------------------------------------------
  72         3       BCDA    2009/8/2
  73         4       OOOO    2009/8/4
  74         4       EFGH    2009/8/4
  75         5       PPPP    2009/8/7
  76         5       IJKL    2009/8/7
  77
  78 echo SELECT * FROM t1 ORDER BY f1 DESC;
  79 ---------------------------------------------------------
  80         t1.f1   t1.f2   t1.f3
  81 ---------------------------------------------------------
  82         9       DCBA    2009/8/9
  83         7       ACBD    2009/8/7
  84         6       QQQQ    2009/8/8
  85         6       MNOP    2009/8/8
  86         5       PPPP    2009/8/7
  87         5       IJKL    2009/8/7
  88         5       CDBA    2009/8/3
  89         4       OOOO    2009/8/4
  90         4       EFGH    2009/8/4
  91         3       NNNN    2009/8/3
  92         3       TUVW    2009/8/3
  93         3       BCDA    2009/8/2
  94         2       MMMM    2009/8/2
  95         2       PQRS    2009/8/2
  96         1       ABCD    2009/8/1
  97
  98 echo SELECT * FROM t1 ORDER BY f1 DESC LIMIT 5;
  99 ---------------------------------------------------------
 100         t1.f1   t1.f2   t1.f3
 101 ---------------------------------------------------------
 102         9       DCBA    2009/8/9
 103         7       ACBD    2009/8/7
 104         6       QQQQ    2009/8/8
 105         6       MNOP    2009/8/8
 106         5       PPPP    2009/8/7
 107
 108 echo SELECT * FROM t1 ORDER BY f1 DESC LIMIT 5 OFFSET 5;
 109 ---------------------------------------------------------
 110         t1.f1   t1.f2   t1.f3
 111 ---------------------------------------------------------
 112         5       IJKL    2009/8/7
 113         5       CDBA    2009/8/3
 114         4       OOOO    2009/8/4
 115         4       EFGH    2009/8/4
 116         3       NNNN    2009/8/3
 117
 118 echo SELECT * FROM t1 ORDER BY f1 ASC, f2 DESC;
 119 ---------------------------------------------------------
 120         t1.f1   t1.f2   t1.f3
 121 ---------------------------------------------------------
 122         1       ABCD    2009/8/1
 123         2       PQRS    2009/8/2
 124         2       MMMM    2009/8/2
 125         3       TUVW    2009/8/3
 126         3       NNNN    2009/8/3
 127         3       BCDA    2009/8/2
 128         4       OOOO    2009/8/4
 129         4       EFGH    2009/8/4
 130         5       PPPP    2009/8/7
 131         5       IJKL    2009/8/7
 132         5       CDBA    2009/8/3
 133         6       QQQQ    2009/8/8
 134         6       MNOP    2009/8/8
 135         7       ACBD    2009/8/7
 136         9       DCBA    2009/8/9
 137
 138 echo SELECT * FROM t1 ORDER BY f1 ASC, f2 DESC LIMIT 5;
 139 ---------------------------------------------------------
 140         t1.f1   t1.f2   t1.f3
 141 ---------------------------------------------------------
 142         1       ABCD    2009/8/1
 143         2       PQRS    2009/8/2
 144         2       MMMM    2009/8/2
 145         3       TUVW    2009/8/3
 146         3       NNNN    2009/8/3
 147
 148 echo SELECT * FROM t1 ORDER BY f1 DESC, f2 ASC LIMIT 5 OFFSET 5;
 149 ---------------------------------------------------------
 150         t1.f1   t1.f2   t1.f3
 151 ---------------------------------------------------------
 152         5       IJKL    2009/8/7
 153         5       PPPP    2009/8/7
 154         4       EFGH    2009/8/4
 155         4       OOOO    2009/8/4
 156         3       BCDA    2009/8/2
 157
 158 Statement Executed