Commits

Kirill Simonov committed d17cfa4

top(): fixes for Oracle and MSSQL, added tests.

Comments (0)

Files changed (6)

src/htsql_mssql/core/tr/compile.py

 from htsql.core.tr.coerce import coerce
 from htsql.core.tr.signature import (CompareSig, AndSig, SortDirectionSig,
                                      RowNumberSig)
-from htsql.core.tr.compile import CompileOrdered
+from htsql.core.tr.compile import CompileOrdered, CompileCovering
 from htsql.core.tr.stitch import arrange, spread
 
 
                           self.flow, kid.baseline, routes)
 
 
+class MSSQLCompileCovering(CompileCovering):
+
+    def clip_root(self, term, order):
+        if self.flow.offset is not None:
+            return self.clip(term, order, [])
+        return super(MSSQLCompileCovering, self).clip_root(term, order)
+
+

src/htsql_oracle/core/tr/compile.py

 from htsql.core.tr.coerce import coerce
 from htsql.core.tr.signature import CompareSig
 from .signature import RowNumSig
-from htsql.core.tr.compile import CompileOrdered
+from htsql.core.tr.compile import CompileOrdered, CompileCovering
 from htsql.core.tr.stitch import arrange, spread
 
 
         return kid
 
 
+class OracleCompileCovering(CompileCovering):
+
+    def clip_root(self, term, order):
+        left_bound = 1
+        if self.flow.offset is not None:
+            left_bound = self.flow.offset+1
+        right_bound = left_bound+1
+        if self.flow.limit is not None:
+            right_bound = left_bound+self.flow.limit
+        term = OrderTerm(self.state.tag(), term, order, None, None,
+                        term.flow, term.baseline, term.routes.copy())
+        term = PermanentTerm(self.state.tag(), term,
+                             term.flow, term.baseline, term.routes.copy())
+        row_num_code = FormulaCode(RowNumSig(), coerce(IntegerDomain()),
+                                   self.flow.binding)
+        right_bound_code = LiteralCode(right_bound, coerce(IntegerDomain()),
+                                       self.flow.binding)
+        right_filter = FormulaCode(CompareSig('<'), coerce(BooleanDomain()),
+                                   self.flow.binding,
+                                   lop=row_num_code, rop=right_bound_code)
+        term = FilterTerm(self.state.tag(), term, right_filter,
+                          term.flow, term.baseline, term.routes.copy())
+        routes = term.routes.copy()
+        row_num_unit = ScalarUnit(row_num_code, self.flow, self.flow.binding)
+        routes[row_num_unit] = term.tag
+        term = PermanentTerm(self.state.tag(), term,
+                             term.flow, term.baseline, routes)
+        left_bound_code = LiteralCode(left_bound, coerce(IntegerDomain()),
+                                      self.flow.binding)
+        left_filter = FormulaCode(CompareSig('>='), coerce(BooleanDomain()),
+                                  self.flow.binding,
+                                  lop=row_num_unit, rop=left_bound_code)
+        term = FilterTerm(self.state.tag(), term, left_filter,
+                          term.flow, term.baseline, term.routes.copy())
+        return term
+
+

test/input/translation.yaml

     - uri: /school.@school
     - uri: /@school.@department.@program.limit(1)
 
+  - title: Top
+    ifndef: [sqlite, mysql]
+    tests:
+    - uri: /school :top
+    - uri: /school :top(3)
+    - uri: /school :top(3,2)
+    - uri: /school{code, count(department)-} :top
+      skip: true
+    - uri: /school{code, count(department)-} :top(3)
+      skip: true
+    - uri: /school{code, count(department)-} :top(3,2)
+      skip: true
+    - uri: /top(school.sort(count(department)-)){code, count(department)}
+    - uri: /top(school.sort(count(department)-),3){code, count(department)}
+    - uri: /top(school.sort(count(department)-),3,2){code, count(department)}
+    - uri: /school{name, count(program),
+                   top(program){title, count(student)}}?campus='old'
+    - uri: /school{name, count(program),
+                   /top(program,3){title, count(student)}}?campus='old'
+    - uri: /school{name, count(program),
+            /top(program,3,2){title, count(student)}}?campus='old'
+    - uri: /school^campus{*, top(^)}
+    - uri: /class^{year, season}
+                {*, /top(^.sort(count(enrollment)-),3).course{title}}
+
   - title: Table Expressions
     tests:
     - uri: /(school?code='art').department

test/output/mssql.yaml

                     CROSS JOIN [ad].[department]
                     CROSS JOIN [ad].[program]
                ORDER BY [school].[code] ASC, [department].[code] ASC, 1 ASC, 2 ASC
+        - id: top
+          tests:
+          - uri: /school :top
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school:top                             |
+               +------+------------------------+--------+
+               | code | name                   | campus |
+              -+------+------------------------+--------+-
+               | art  | School of Art & Design | old    |
+
+               ----
+               /school:top
+               SELECT [school].[code],
+                      [school].[name],
+                      [school].[campus]
+               FROM (SELECT TOP 1
+                            [school].[code],
+                            [school].[name],
+                            [school].[campus]
+                     FROM [ad].[school]
+                     ORDER BY 1 ASC) AS [school]
+               ORDER BY 1 ASC
+          - uri: /school :top(3)
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school:top(3)                          |
+               +------+------------------------+--------+
+               | code | name                   | campus |
+              -+------+------------------------+--------+-
+               | art  | School of Art & Design | old    |
+               | bus  | School of Business     | south  |
+               | edu  | College of Education   | old    |
+
+               ----
+               /school:top(3)
+               SELECT [school].[code],
+                      [school].[name],
+                      [school].[campus]
+               FROM (SELECT TOP 3
+                            [school].[code],
+                            [school].[name],
+                            [school].[campus]
+                     FROM [ad].[school]
+                     ORDER BY 1 ASC) AS [school]
+               ORDER BY 1 ASC
+          - uri: /school :top(3,2)
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school:top(3,2)                               |
+               +------+-------------------------------+--------+
+               | code | name                          | campus |
+              -+------+-------------------------------+--------+-
+               | edu  | College of Education          | old    |
+               | eng  | School of Engineering         | north  |
+               | la   | School of Arts and Humanities | old    |
+
+               ----
+               /school:top(3,2)
+               SELECT [school].[code],
+                      [school].[name],
+                      [school].[campus]
+               FROM (SELECT [school].[code],
+                            [school].[name],
+                            [school].[campus],
+                            ROW_NUMBER() OVER (ORDER BY [school].[code] ASC) AS [top]
+                     FROM [ad].[school]) AS [school]
+               WHERE ([school].[top] >= 3)
+                     AND ([school].[top] < 6)
+               ORDER BY 1 ASC
+          - uri: /top(school.sort(count(department)-)){code, count(department)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | top(school.sort(count(department)-)) |
+               +------------+-------------------------+
+               | code       | count(department)       |
+              -+------------+-------------------------+-
+               | la         |                       6 |
+
+               ----
+               /top(school.sort(count(department)-)){code,count(department)}
+               SELECT [school_1].[code],
+                      COALESCE([department].[count], 0)
+               FROM (SELECT TOP 1
+                            [school].[code]
+                     FROM [ad].[school]
+                          LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                                  [department].[school_code]
+                                           FROM [ad].[department]
+                                           GROUP BY [department].[school_code]) AS [department]
+                                          ON ([school].[code] = [department].[school_code])
+                     ORDER BY COALESCE([department].[count], 0) DESC, 1 ASC) AS [school_1]
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                            [department].[school_code]
+                                     FROM [ad].[department]
+                                     GROUP BY [department].[school_code]) AS [department]
+                                    ON ([school_1].[code] = [department].[school_code])
+                    INNER JOIN (SELECT TOP 1
+                                       COALESCE([department].[count], 0) AS [count],
+                                       [school].[code]
+                                FROM [ad].[school]
+                                     LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                                             [department].[school_code]
+                                                      FROM [ad].[department]
+                                                      GROUP BY [department].[school_code]) AS [department]
+                                                     ON ([school].[code] = [department].[school_code])
+                                ORDER BY 1 DESC, 2 ASC) AS [school_2]
+                               ON ([school_1].[code] = [school_2].[code])
+               ORDER BY [school_2].[count] DESC, 1 ASC
+          - uri: /top(school.sort(count(department)-),3){code, count(department)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | top(school.sort(count(department)-),3) |
+               +-------------+--------------------------+
+               | code        | count(department)        |
+              -+-------------+--------------------------+-
+               | la          |                        6 |
+               | eng         |                        4 |
+               | mus         |                        4 |
+
+               ----
+               /top(school.sort(count(department)-),3){code,count(department)}
+               SELECT [school_1].[code],
+                      COALESCE([department].[count], 0)
+               FROM (SELECT TOP 3
+                            [school].[code]
+                     FROM [ad].[school]
+                          LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                                  [department].[school_code]
+                                           FROM [ad].[department]
+                                           GROUP BY [department].[school_code]) AS [department]
+                                          ON ([school].[code] = [department].[school_code])
+                     ORDER BY COALESCE([department].[count], 0) DESC, 1 ASC) AS [school_1]
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                            [department].[school_code]
+                                     FROM [ad].[department]
+                                     GROUP BY [department].[school_code]) AS [department]
+                                    ON ([school_1].[code] = [department].[school_code])
+                    INNER JOIN (SELECT TOP 3
+                                       COALESCE([department].[count], 0) AS [count],
+                                       [school].[code]
+                                FROM [ad].[school]
+                                     LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                                             [department].[school_code]
+                                                      FROM [ad].[department]
+                                                      GROUP BY [department].[school_code]) AS [department]
+                                                     ON ([school].[code] = [department].[school_code])
+                                ORDER BY 1 DESC, 2 ASC) AS [school_2]
+                               ON ([school_1].[code] = [school_2].[code])
+               ORDER BY [school_2].[count] DESC, 1 ASC
+          - uri: /top(school.sort(count(department)-),3,2){code, count(department)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | top(school.sort(count(department)-),3,2) |
+               +--------------+---------------------------+
+               | code         | count(department)         |
+              -+--------------+---------------------------+-
+               | mus          |                         4 |
+               | ns           |                         4 |
+               | bus          |                         3 |
+
+               ----
+               /top(school.sort(count(department)-),3,2){code,count(department)}
+               SELECT [school_1].[code],
+                      COALESCE([department].[count], 0)
+               FROM (SELECT [school].[code],
+                            ROW_NUMBER() OVER (ORDER BY COALESCE([department].[count], 0) DESC, [school].[code] ASC) AS [top]
+                     FROM [ad].[school]
+                          LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                                  [department].[school_code]
+                                           FROM [ad].[department]
+                                           GROUP BY [department].[school_code]) AS [department]
+                                          ON ([school].[code] = [department].[school_code])) AS [school_1]
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                            [department].[school_code]
+                                     FROM [ad].[department]
+                                     GROUP BY [department].[school_code]) AS [department]
+                                    ON ([school_1].[code] = [department].[school_code])
+                    INNER JOIN (SELECT COALESCE([school].[count], 0) AS [count],
+                                       [school].[code]
+                                FROM (SELECT [department].[count],
+                                             [school].[code],
+                                             ROW_NUMBER() OVER (ORDER BY COALESCE([department].[count], 0) DESC, [school].[code] ASC) AS [top]
+                                      FROM [ad].[school]
+                                           LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                                                   [department].[school_code]
+                                                            FROM [ad].[department]
+                                                            GROUP BY [department].[school_code]) AS [department]
+                                                           ON ([school].[code] = [department].[school_code])) AS [school]
+                                WHERE ([school].[top] >= 3)
+                                      AND ([school].[top] < 6)) AS [school_2]
+                               ON ([school_1].[code] = [school_2].[code])
+               WHERE ([school_1].[top] >= 3)
+                     AND ([school_1].[top] < 6)
+               ORDER BY [school_2].[count] DESC, 1 ASC
+          - uri: /school{name, count(program), top(program){title, count(student)}}?campus='old'
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school                                                                                             |
+               +-------------------------------+----------------+---------------------------------------------------+
+               |                               |                | top(program)                                      |
+               |                               |                +----------------------------------+----------------+
+               | name                          | count(program) | title                            | count(student) |
+              -+-------------------------------+----------------+----------------------------------+----------------+-
+               | School of Art & Design        |              3 | Post Baccalaureate in Art        |             16 |
+               :                               :                : History                          :                :
+               | College of Education          |              7 | Master of Arts in Education      |             10 |
+               :                               :                : Leadership                       :                :
+               | School of Arts and Humanities |              9 | Master of Arts in English        |             13 |
+               | School of Natural Sciences    |              6 | Masters of Science in            |             13 |
+               :                               :                : Mathematics                      :                :
+
+               ----
+               /school{name,count(program),top(program){title,count(student)}}?campus='old'
+               SELECT [school].[name],
+                      COALESCE([program_1].[count], 0),
+                      [program_2].[program],
+                      [program_2].[title],
+                      [program_2].[count]
+               FROM [ad].[school]
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                            [program].[school_code]
+                                     FROM [ad].[program]
+                                     GROUP BY [program].[school_code]) AS [program_1]
+                                    ON ([school].[code] = [program_1].[school_code])
+                    LEFT OUTER JOIN (SELECT 1 AS [program],
+                                            [program].[title],
+                                            COALESCE([student].[count], 0) AS [count],
+                                            [program].[school_code]
+                                     FROM (SELECT [program].[title],
+                                                  [program].[school_code],
+                                                  [program].[code],
+                                                  ROW_NUMBER() OVER (PARTITION BY [program].[school_code] ORDER BY [program].[school_code] ASC, [program].[code] ASC) AS [top]
+                                           FROM [ad].[program]) AS [program]
+                                          LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                                                  [student].[school_code],
+                                                                  [student].[program_code]
+                                                           FROM [ed].[student]
+                                                           GROUP BY [student].[school_code], [student].[program_code]) AS [student]
+                                                          ON (([program].[school_code] = [student].[school_code]) AND ([program].[code] = [student].[program_code]))
+                                     WHERE ([program].[top] >= 1)
+                                           AND ([program].[top] < 2)) AS [program_2]
+                                    ON ([school].[code] = [program_2].[school_code])
+               WHERE ([school].[campus] = 'old')
+               ORDER BY [school].[code] ASC
+          - uri: /school{name, count(program), /top(program,3){title, count(student)}}?campus='old'
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school                                                                                             |
+               +-------------------------------+----------------+---------------------------------------------------+
+               |                               |                | top(program,3)                                    |
+               |                               |                +----------------------------------+----------------+
+               | name                          | count(program) | title                            | count(student) |
+              -+-------------------------------+----------------+----------------------------------+----------------+-
+               | School of Art & Design        |              3 | Post Baccalaureate in Art        |             16 |
+               :                               :                : History                          :                :
+               :                               :                | Bachelor of Arts in Art History  |             20 |
+               :                               :                | Bachelor of Arts in Studio Art   |             26 |
+               | College of Education          |              7 | Master of Arts in Education      |             10 |
+               :                               :                : Leadership                       :                :
+               :                               :                | M.S. in Education                |             11 |
+               :                               :                | Master of Arts in Literacy       |             15 |
+               :                               :                : Education                        :                :
+               | School of Arts and Humanities |              9 | Master of Arts in English        |             13 |
+               :                               :                | Master of Arts in Modern         |             10 |
+               :                               :                : Languages                        :                :
+               :                               :                | Master of Arts in Science        |             11 |
+               :                               :                : Teaching                         :                :
+               | School of Natural Sciences    |              6 | Masters of Science in            |             13 |
+               :                               :                : Mathematics                      :                :
+               :                               :                | Doctorate of Science in          |             10 |
+               :                               :                : Mathematics                      :                :
+               :                               :                | Bachelor of Science in Astronomy |              5 |
+
+               ----
+               /school{name,count(program),/top(program,3){title,count(student)}}?campus='old'
+               SELECT [school].[name],
+                      COALESCE([program].[count], 0),
+                      [school].[code]
+               FROM [ad].[school]
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                            [program].[school_code]
+                                     FROM [ad].[program]
+                                     GROUP BY [program].[school_code]) AS [program]
+                                    ON ([school].[code] = [program].[school_code])
+               WHERE ([school].[campus] = 'old')
+               ORDER BY 3 ASC
+
+                 SELECT [program].[title],
+                        COALESCE([student].[count], 0),
+                        [school].[code]
+                 FROM [ad].[school]
+                      INNER JOIN (SELECT [program].[title],
+                                         [program].[school_code],
+                                         [program].[code]
+                                  FROM (SELECT [program].[title],
+                                               [program].[school_code],
+                                               [program].[code],
+                                               ROW_NUMBER() OVER (PARTITION BY [program].[school_code] ORDER BY [program].[school_code] ASC, [program].[code] ASC) AS [top]
+                                        FROM [ad].[program]) AS [program]
+                                  WHERE ([program].[top] >= 1)
+                                        AND ([program].[top] < 4)) AS [program]
+                                 ON ([school].[code] = [program].[school_code])
+                      LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                              [student].[school_code],
+                                              [student].[program_code]
+                                       FROM [ed].[student]
+                                       GROUP BY [student].[school_code], [student].[program_code]) AS [student]
+                                      ON (([program].[school_code] = [student].[school_code]) AND ([program].[code] = [student].[program_code]))
+                 WHERE ([school].[campus] = 'old')
+                 ORDER BY 3 ASC, [program].[school_code] ASC, [program].[code] ASC
+          - uri: /school{name, count(program), /top(program,3,2){title, count(student)}}?campus='old'
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school                                                                                             |
+               +-------------------------------+----------------+---------------------------------------------------+
+               |                               |                | top(program,3,2)                                  |
+               |                               |                +----------------------------------+----------------+
+               | name                          | count(program) | title                            | count(student) |
+              -+-------------------------------+----------------+----------------------------------+----------------+-
+               | School of Art & Design        |              3 | Bachelor of Arts in Studio Art   |             26 |
+               | College of Education          |              7 | Master of Arts in Literacy       |             15 |
+               :                               :                : Education                        :                :
+               :                               :                | Master of Arts in Teaching       |             13 |
+               :                               :                | Certificate in Science Teaching  |              8 |
+               | School of Arts and Humanities |              9 | Master of Arts in Science        |             11 |
+               :                               :                : Teaching                         :                :
+               :                               :                | Science Writing                  |              5 |
+               :                               :                | Bachelor of Arts in English      |              8 |
+               | School of Natural Sciences    |              6 | Bachelor of Science in Astronomy |              5 |
+               :                               :                | Bachelor of Science in Chemistry |             11 |
+               :                               :                | Bachelor of Science in           |             13 |
+               :                               :                : Mathematics                      :                :
+
+               ----
+               /school{name,count(program),/top(program,3,2){title,count(student)}}?campus='old'
+               SELECT [school].[name],
+                      COALESCE([program].[count], 0),
+                      [school].[code]
+               FROM [ad].[school]
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                            [program].[school_code]
+                                     FROM [ad].[program]
+                                     GROUP BY [program].[school_code]) AS [program]
+                                    ON ([school].[code] = [program].[school_code])
+               WHERE ([school].[campus] = 'old')
+               ORDER BY 3 ASC
+
+                 SELECT [program].[title],
+                        COALESCE([student].[count], 0),
+                        [school].[code]
+                 FROM [ad].[school]
+                      INNER JOIN (SELECT [program].[title],
+                                         [program].[school_code],
+                                         [program].[code]
+                                  FROM (SELECT [program].[title],
+                                               [program].[school_code],
+                                               [program].[code],
+                                               ROW_NUMBER() OVER (PARTITION BY [program].[school_code] ORDER BY [program].[school_code] ASC, [program].[code] ASC) AS [top]
+                                        FROM [ad].[program]) AS [program]
+                                  WHERE ([program].[top] >= 3)
+                                        AND ([program].[top] < 6)) AS [program]
+                                 ON ([school].[code] = [program].[school_code])
+                      LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                              [student].[school_code],
+                                              [student].[program_code]
+                                       FROM [ed].[student]
+                                       GROUP BY [student].[school_code], [student].[program_code]) AS [student]
+                                      ON (([program].[school_code] = [student].[school_code]) AND ([program].[code] = [student].[program_code]))
+                 WHERE ([school].[campus] = 'old')
+                 ORDER BY 3 ASC, [program].[school_code] ASC, [program].[code] ASC
+          - uri: /school^campus{*, top(^)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school^campus                                   |
+               +--------+----------------------------------------+
+               |        | top(^)                                 |
+               |        +------+------------------------+--------+
+               | campus | code | name                   | campus |
+              -+--------+------+------------------------+--------+-
+               | north  | eng  | School of Engineering  | north  |
+               | old    | art  | School of Art & Design | old    |
+               | south  | bus  | School of Business     | south  |
+
+               ----
+               /school^campus{*,top(^)}
+               SELECT [school_1].[campus],
+                      [school_2].[code],
+                      [school_2].[name],
+                      [school_2].[campus]
+               FROM (SELECT [school].[campus]
+                     FROM [ad].[school]
+                     WHERE ([school].[campus] IS NOT NULL)
+                     GROUP BY [school].[campus]) AS [school_1]
+                    INNER JOIN (SELECT [school].[code],
+                                       [school].[name],
+                                       [school].[campus]
+                                FROM (SELECT [school].[code],
+                                             [school].[name],
+                                             [school].[campus],
+                                             ROW_NUMBER() OVER (PARTITION BY [school].[campus] ORDER BY [school].[code] ASC) AS [top]
+                                      FROM [ad].[school]) AS [school]
+                                WHERE ([school].[top] >= 1)
+                                      AND ([school].[top] < 2)) AS [school_2]
+                               ON ([school_1].[campus] = [school_2].[campus])
+               ORDER BY 1 ASC
+          - uri: /class^{year, season} {*, /top(^.sort(count(enrollment)-),3).course{title}}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | class^{year,season}                              |
+               +------+--------+----------------------------------+
+               |      |        | course                           |
+               |      |        +----------------------------------+
+               | year | season | title                            |
+              -+------+--------+----------------------------------+-
+               | 2007 | fall   | College Algebra I                |
+               :      :        | History of Art Criticism I       |
+               :      :        | Language Basics                  |
+               | 2007 | spring | College Algebra II               |
+               :      :        | Introduction to Education        |
+               :      :        | Islam History                    |
+               | 2008 | fall   | College Algebra I                |
+               :      :        | Art in Therapy                   |
+               :      :        | History of Art Criticism I       |
+               | 2008 | spring | College Algebra II               |
+               :      :        | History of Computing             |
+               :      :        | Asian Architecture               |
+               | 2009 | fall   | Museum and Gallery Management    |
+               :      :        | Introduction to Biomedical       |
+               :      :        : Engineering                      :
+               :      :        | Bioengineering Seminar           |
+               | 2009 | spring | Introduction to Education        |
+               :      :        | Historical Perspective on the    |
+               :      :        : Constitution                     :
+               :      :        | College Algebra II               |
+               | 2010 | fall   | History of Art Criticism I       |
+               :      :        | Arts of Asia                     |
+               :      :        | Contemporary Latin American Art  |
+               | 2010 | spring | Practical Bookkeeping            |
+               :      :        | Asian Architecture               |
+               :      :        | Materials and Construction in    |
+               :      :        : European Art                     :
+
+               ----
+               /class^{year,season}{*,/top(^.sort(count(enrollment)-),3).course{title}}
+               SELECT [class].[year],
+                      [class].[season]
+               FROM [cd].[class]
+               WHERE ([class].[year] IS NOT NULL)
+                     AND ([class].[season] IS NOT NULL)
+               GROUP BY [class].[year], [class].[season]
+               ORDER BY 1 ASC, 2 ASC
+
+                 SELECT [course].[title],
+                        [class_1].[year],
+                        [class_1].[season]
+                 FROM (SELECT [class].[year],
+                              [class].[season]
+                       FROM [cd].[class]
+                       WHERE ([class].[year] IS NOT NULL)
+                             AND ([class].[season] IS NOT NULL)
+                       GROUP BY [class].[year], [class].[season]) AS [class_1]
+                      INNER JOIN (SELECT [class].[department_code],
+                                         [class].[course_no],
+                                         [class].[year],
+                                         [class].[season],
+                                         [class].[section]
+                                  FROM (SELECT [class].[department_code],
+                                               [class].[course_no],
+                                               [class].[year],
+                                               [class].[season],
+                                               [class].[section],
+                                               ROW_NUMBER() OVER (PARTITION BY [class].[year], [class].[season] ORDER BY COALESCE([enrollment].[count], 0) DESC, [class].[department_code] ASC, [class].[course_no] ASC, [class].[year] ASC, [class].[season] ASC, [class].[section] ASC) AS [top]
+                                        FROM [cd].[class]
+                                             LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                                                     [enrollment].[class_seq]
+                                                              FROM [ed].[enrollment]
+                                                              GROUP BY [enrollment].[class_seq]) AS [enrollment]
+                                                             ON ([class].[class_seq] = [enrollment].[class_seq])) AS [class]
+                                  WHERE ([class].[top] >= 1)
+                                        AND ([class].[top] < 4)) AS [class_2]
+                                 ON (([class_1].[year] = [class_2].[year]) AND ([class_1].[season] = [class_2].[season]))
+                      INNER JOIN [ad].[course]
+                                 ON (([class_2].[department_code] = [course].[department_code]) AND ([class_2].[course_no] = [course].[no]))
+                      INNER JOIN (SELECT COALESCE([class].[count], 0) AS [count],
+                                         [class].[department_code],
+                                         [class].[course_no],
+                                         [class].[year],
+                                         [class].[season],
+                                         [class].[section]
+                                  FROM (SELECT [enrollment].[count],
+                                               [class].[department_code],
+                                               [class].[course_no],
+                                               [class].[year],
+                                               [class].[season],
+                                               [class].[section],
+                                               ROW_NUMBER() OVER (PARTITION BY [class].[year], [class].[season] ORDER BY COALESCE([enrollment].[count], 0) DESC, [class].[department_code] ASC, [class].[course_no] ASC, [class].[year] ASC, [class].[season] ASC, [class].[section] ASC) AS [top]
+                                        FROM [cd].[class]
+                                             LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                                                     [enrollment].[class_seq]
+                                                              FROM [ed].[enrollment]
+                                                              GROUP BY [enrollment].[class_seq]) AS [enrollment]
+                                                             ON ([class].[class_seq] = [enrollment].[class_seq])) AS [class]
+                                  WHERE ([class].[top] >= 1)
+                                        AND ([class].[top] < 4)) AS [class_3]
+                                 ON (([class_2].[department_code] = [class_3].[department_code]) AND ([class_2].[course_no] = [class_3].[course_no]) AND ([class_2].[year] = [class_3].[year]) AND ([class_2].[season] = [class_3].[season]) AND ([class_2].[section] = [class_3].[section]))
+                 ORDER BY 2 ASC, 3 ASC, [class_3].[count] DESC, [class_2].[department_code] ASC, [class_2].[course_no] ASC, [class_2].[year] ASC, [class_2].[season] ASC, [class_2].[section] ASC
         - id: table-expressions
           tests:
           - uri: /(school?code='art').department

test/output/oracle.yaml

                            ORDER BY 6 ASC, 7 ASC, 1 ASC, 2 ASC) "PROGRAM"
                      WHERE (ROWNUM < 2)) "PROGRAM"
                ORDER BY "PROGRAM"."CODE_2" ASC, "PROGRAM"."CODE_3" ASC, 1 ASC, 2 ASC
+        - id: top
+          tests:
+          - uri: /school :top
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school:top                             |
+               +------+------------------------+--------+
+               | code | name                   | campus |
+              -+------+------------------------+--------+-
+               | art  | School of Art & Design | old    |
+
+               ----
+               /school:top
+               SELECT "SCHOOL"."CODE",
+                      "SCHOOL"."NAME",
+                      "SCHOOL"."CAMPUS"
+               FROM (SELECT "SCHOOL"."CODE",
+                            "SCHOOL"."NAME",
+                            "SCHOOL"."CAMPUS",
+                            ROWNUM AS "top"
+                     FROM (SELECT "SCHOOL"."CODE",
+                                  "SCHOOL"."NAME",
+                                  "SCHOOL"."CAMPUS"
+                           FROM "SCHOOL"
+                           ORDER BY 1 ASC) "SCHOOL"
+                     WHERE (ROWNUM < 2)) "SCHOOL"
+               WHERE ("SCHOOL"."top" >= 1)
+               ORDER BY 1 ASC
+          - uri: /school :top(3)
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school:top(3)                          |
+               +------+------------------------+--------+
+               | code | name                   | campus |
+              -+------+------------------------+--------+-
+               | art  | School of Art & Design | old    |
+               | bus  | School of Business     | south  |
+               | edu  | College of Education   | old    |
+
+               ----
+               /school:top(3)
+               SELECT "SCHOOL"."CODE",
+                      "SCHOOL"."NAME",
+                      "SCHOOL"."CAMPUS"
+               FROM (SELECT "SCHOOL"."CODE",
+                            "SCHOOL"."NAME",
+                            "SCHOOL"."CAMPUS",
+                            ROWNUM AS "top"
+                     FROM (SELECT "SCHOOL"."CODE",
+                                  "SCHOOL"."NAME",
+                                  "SCHOOL"."CAMPUS"
+                           FROM "SCHOOL"
+                           ORDER BY 1 ASC) "SCHOOL"
+                     WHERE (ROWNUM < 4)) "SCHOOL"
+               WHERE ("SCHOOL"."top" >= 1)
+               ORDER BY 1 ASC
+          - uri: /school :top(3,2)
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school:top(3,2)                               |
+               +------+-------------------------------+--------+
+               | code | name                          | campus |
+              -+------+-------------------------------+--------+-
+               | edu  | College of Education          | old    |
+               | eng  | School of Engineering         | north  |
+               | la   | School of Arts and Humanities | old    |
+
+               ----
+               /school:top(3,2)
+               SELECT "SCHOOL"."CODE",
+                      "SCHOOL"."NAME",
+                      "SCHOOL"."CAMPUS"
+               FROM (SELECT "SCHOOL"."CODE",
+                            "SCHOOL"."NAME",
+                            "SCHOOL"."CAMPUS",
+                            ROWNUM AS "top"
+                     FROM (SELECT "SCHOOL"."CODE",
+                                  "SCHOOL"."NAME",
+                                  "SCHOOL"."CAMPUS"
+                           FROM "SCHOOL"
+                           ORDER BY 1 ASC) "SCHOOL"
+                     WHERE (ROWNUM < 6)) "SCHOOL"
+               WHERE ("SCHOOL"."top" >= 3)
+               ORDER BY 1 ASC
+          - uri: /top(school.sort(count(department)-)){code, count(department)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | top(school.sort(count(department)-)) |
+               +------------+-------------------------+
+               | code       | count(department)       |
+              -+------------+-------------------------+-
+               | la         |                       6 |
+
+               ----
+               /top(school.sort(count(department)-)){code,count(department)}
+               SELECT "SCHOOL_1"."CODE",
+                      COALESCE("DEPARTMENT"."count", 0)
+               FROM (SELECT "SCHOOL"."CODE",
+                            ROWNUM AS "top"
+                     FROM (SELECT "SCHOOL"."CODE"
+                           FROM "SCHOOL"
+                                LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                                        "DEPARTMENT"."SCHOOL_CODE"
+                                                 FROM "DEPARTMENT"
+                                                 GROUP BY "DEPARTMENT"."SCHOOL_CODE") "DEPARTMENT"
+                                                ON ("SCHOOL"."CODE" = "DEPARTMENT"."SCHOOL_CODE")
+                           ORDER BY COALESCE("DEPARTMENT"."count", 0) DESC, 1 ASC) "SCHOOL"
+                     WHERE (ROWNUM < 2)) "SCHOOL_1"
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "DEPARTMENT"."SCHOOL_CODE"
+                                     FROM "DEPARTMENT"
+                                     GROUP BY "DEPARTMENT"."SCHOOL_CODE") "DEPARTMENT"
+                                    ON ("SCHOOL_1"."CODE" = "DEPARTMENT"."SCHOOL_CODE")
+                    INNER JOIN (SELECT COALESCE("SCHOOL"."count", 0) AS "count",
+                                       "SCHOOL"."CODE"
+                                FROM (SELECT "SCHOOL"."count",
+                                             "SCHOOL"."CODE",
+                                             ROWNUM AS "top"
+                                      FROM (SELECT "DEPARTMENT"."count",
+                                                   "SCHOOL"."CODE"
+                                            FROM "SCHOOL"
+                                                 LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                                                         "DEPARTMENT"."SCHOOL_CODE"
+                                                                  FROM "DEPARTMENT"
+                                                                  GROUP BY "DEPARTMENT"."SCHOOL_CODE") "DEPARTMENT"
+                                                                 ON ("SCHOOL"."CODE" = "DEPARTMENT"."SCHOOL_CODE")
+                                            ORDER BY COALESCE("DEPARTMENT"."count", 0) DESC, 2 ASC) "SCHOOL"
+                                      WHERE (ROWNUM < 2)) "SCHOOL"
+                                WHERE ("SCHOOL"."top" >= 1)) "SCHOOL_2"
+                               ON ("SCHOOL_1"."CODE" = "SCHOOL_2"."CODE")
+               WHERE ("SCHOOL_1"."top" >= 1)
+               ORDER BY "SCHOOL_2"."count" DESC, 1 ASC
+          - uri: /top(school.sort(count(department)-),3){code, count(department)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | top(school.sort(count(department)-),3) |
+               +-------------+--------------------------+
+               | code        | count(department)        |
+              -+-------------+--------------------------+-
+               | la          |                        6 |
+               | eng         |                        4 |
+               | mus         |                        4 |
+
+               ----
+               /top(school.sort(count(department)-),3){code,count(department)}
+               SELECT "SCHOOL_1"."CODE",
+                      COALESCE("DEPARTMENT"."count", 0)
+               FROM (SELECT "SCHOOL"."CODE",
+                            ROWNUM AS "top"
+                     FROM (SELECT "SCHOOL"."CODE"
+                           FROM "SCHOOL"
+                                LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                                        "DEPARTMENT"."SCHOOL_CODE"
+                                                 FROM "DEPARTMENT"
+                                                 GROUP BY "DEPARTMENT"."SCHOOL_CODE") "DEPARTMENT"
+                                                ON ("SCHOOL"."CODE" = "DEPARTMENT"."SCHOOL_CODE")
+                           ORDER BY COALESCE("DEPARTMENT"."count", 0) DESC, 1 ASC) "SCHOOL"
+                     WHERE (ROWNUM < 4)) "SCHOOL_1"
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "DEPARTMENT"."SCHOOL_CODE"
+                                     FROM "DEPARTMENT"
+                                     GROUP BY "DEPARTMENT"."SCHOOL_CODE") "DEPARTMENT"
+                                    ON ("SCHOOL_1"."CODE" = "DEPARTMENT"."SCHOOL_CODE")
+                    INNER JOIN (SELECT COALESCE("SCHOOL"."count", 0) AS "count",
+                                       "SCHOOL"."CODE"
+                                FROM (SELECT "SCHOOL"."count",
+                                             "SCHOOL"."CODE",
+                                             ROWNUM AS "top"
+                                      FROM (SELECT "DEPARTMENT"."count",
+                                                   "SCHOOL"."CODE"
+                                            FROM "SCHOOL"
+                                                 LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                                                         "DEPARTMENT"."SCHOOL_CODE"
+                                                                  FROM "DEPARTMENT"
+                                                                  GROUP BY "DEPARTMENT"."SCHOOL_CODE") "DEPARTMENT"
+                                                                 ON ("SCHOOL"."CODE" = "DEPARTMENT"."SCHOOL_CODE")
+                                            ORDER BY COALESCE("DEPARTMENT"."count", 0) DESC, 2 ASC) "SCHOOL"
+                                      WHERE (ROWNUM < 4)) "SCHOOL"
+                                WHERE ("SCHOOL"."top" >= 1)) "SCHOOL_2"
+                               ON ("SCHOOL_1"."CODE" = "SCHOOL_2"."CODE")
+               WHERE ("SCHOOL_1"."top" >= 1)
+               ORDER BY "SCHOOL_2"."count" DESC, 1 ASC
+          - uri: /top(school.sort(count(department)-),3,2){code, count(department)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | top(school.sort(count(department)-),3,2) |
+               +--------------+---------------------------+
+               | code         | count(department)         |
+              -+--------------+---------------------------+-
+               | mus          |                         4 |
+               | ns           |                         4 |
+               | bus          |                         3 |
+
+               ----
+               /top(school.sort(count(department)-),3,2){code,count(department)}
+               SELECT "SCHOOL_1"."CODE",
+                      COALESCE("DEPARTMENT"."count", 0)
+               FROM (SELECT "SCHOOL"."CODE",
+                            ROWNUM AS "top"
+                     FROM (SELECT "SCHOOL"."CODE"
+                           FROM "SCHOOL"
+                                LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                                        "DEPARTMENT"."SCHOOL_CODE"
+                                                 FROM "DEPARTMENT"
+                                                 GROUP BY "DEPARTMENT"."SCHOOL_CODE") "DEPARTMENT"
+                                                ON ("SCHOOL"."CODE" = "DEPARTMENT"."SCHOOL_CODE")
+                           ORDER BY COALESCE("DEPARTMENT"."count", 0) DESC, 1 ASC) "SCHOOL"
+                     WHERE (ROWNUM < 6)) "SCHOOL_1"
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "DEPARTMENT"."SCHOOL_CODE"
+                                     FROM "DEPARTMENT"
+                                     GROUP BY "DEPARTMENT"."SCHOOL_CODE") "DEPARTMENT"
+                                    ON ("SCHOOL_1"."CODE" = "DEPARTMENT"."SCHOOL_CODE")
+                    INNER JOIN (SELECT COALESCE("SCHOOL"."count", 0) AS "count",
+                                       "SCHOOL"."CODE"
+                                FROM (SELECT "SCHOOL"."count",
+                                             "SCHOOL"."CODE",
+                                             ROWNUM AS "top"
+                                      FROM (SELECT "DEPARTMENT"."count",
+                                                   "SCHOOL"."CODE"
+                                            FROM "SCHOOL"
+                                                 LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                                                         "DEPARTMENT"."SCHOOL_CODE"
+                                                                  FROM "DEPARTMENT"
+                                                                  GROUP BY "DEPARTMENT"."SCHOOL_CODE") "DEPARTMENT"
+                                                                 ON ("SCHOOL"."CODE" = "DEPARTMENT"."SCHOOL_CODE")
+                                            ORDER BY COALESCE("DEPARTMENT"."count", 0) DESC, 2 ASC) "SCHOOL"
+                                      WHERE (ROWNUM < 6)) "SCHOOL"
+                                WHERE ("SCHOOL"."top" >= 3)) "SCHOOL_2"
+                               ON ("SCHOOL_1"."CODE" = "SCHOOL_2"."CODE")
+               WHERE ("SCHOOL_1"."top" >= 3)
+               ORDER BY "SCHOOL_2"."count" DESC, 1 ASC
+          - uri: /school{name, count(program), top(program){title, count(student)}}?campus='old'
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school                                                                                             |
+               +-------------------------------+----------------+---------------------------------------------------+
+               |                               |                | top(program)                                      |
+               |                               |                +----------------------------------+----------------+
+               | name                          | count(program) | title                            | count(student) |
+              -+-------------------------------+----------------+----------------------------------+----------------+-
+               | School of Art & Design        |              3 | Post Baccalaureate in Art        |             16 |
+               :                               :                : History                          :                :
+               | College of Education          |              7 | Master of Arts in Education      |             10 |
+               :                               :                : Leadership                       :                :
+               | School of Arts and Humanities |              9 | Master of Arts in English        |             13 |
+               | School of Natural Sciences    |              6 | Masters of Science in            |             13 |
+               :                               :                : Mathematics                      :                :
+
+               ----
+               /school{name,count(program),top(program){title,count(student)}}?campus='old'
+               SELECT "SCHOOL"."NAME",
+                      COALESCE("PROGRAM_1"."count", 0),
+                      "PROGRAM_2"."program",
+                      "PROGRAM_2"."TITLE",
+                      "PROGRAM_2"."count"
+               FROM "SCHOOL"
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "PROGRAM"."SCHOOL_CODE"
+                                     FROM "PROGRAM"
+                                     GROUP BY "PROGRAM"."SCHOOL_CODE") "PROGRAM_1"
+                                    ON ("SCHOOL"."CODE" = "PROGRAM_1"."SCHOOL_CODE")
+                    LEFT OUTER JOIN (SELECT 1 AS "program",
+                                            "PROGRAM"."TITLE",
+                                            COALESCE("STUDENT"."count", 0) AS "count",
+                                            "PROGRAM"."SCHOOL_CODE"
+                                     FROM (SELECT "PROGRAM"."TITLE",
+                                                  "PROGRAM"."SCHOOL_CODE",
+                                                  "PROGRAM"."CODE",
+                                                  ROW_NUMBER() OVER (PARTITION BY "PROGRAM"."SCHOOL_CODE" ORDER BY "PROGRAM"."SCHOOL_CODE" ASC, "PROGRAM"."CODE" ASC) AS "top"
+                                           FROM "PROGRAM") "PROGRAM"
+                                          LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                                                  "STUDENT"."SCHOOL_CODE",
+                                                                  "STUDENT"."PROGRAM_CODE"
+                                                           FROM "STUDENT"
+                                                           GROUP BY "STUDENT"."SCHOOL_CODE", "STUDENT"."PROGRAM_CODE") "STUDENT"
+                                                          ON (("PROGRAM"."SCHOOL_CODE" = "STUDENT"."SCHOOL_CODE") AND ("PROGRAM"."CODE" = "STUDENT"."PROGRAM_CODE"))
+                                     WHERE ("PROGRAM"."top" >= 1)
+                                           AND ("PROGRAM"."top" < 2)) "PROGRAM_2"
+                                    ON ("SCHOOL"."CODE" = "PROGRAM_2"."SCHOOL_CODE")
+               WHERE ("SCHOOL"."CAMPUS" = 'old')
+               ORDER BY "SCHOOL"."CODE" ASC
+          - uri: /school{name, count(program), /top(program,3){title, count(student)}}?campus='old'
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school                                                                                             |
+               +-------------------------------+----------------+---------------------------------------------------+
+               |                               |                | top(program,3)                                    |
+               |                               |                +----------------------------------+----------------+
+               | name                          | count(program) | title                            | count(student) |
+              -+-------------------------------+----------------+----------------------------------+----------------+-
+               | School of Art & Design        |              3 | Post Baccalaureate in Art        |             16 |
+               :                               :                : History                          :                :
+               :                               :                | Bachelor of Arts in Art History  |             20 |
+               :                               :                | Bachelor of Arts in Studio Art   |             26 |
+               | College of Education          |              7 | Master of Arts in Education      |             10 |
+               :                               :                : Leadership                       :                :
+               :                               :                | M.S. in Education                |             11 |
+               :                               :                | Master of Arts in Literacy       |             15 |
+               :                               :                : Education                        :                :
+               | School of Arts and Humanities |              9 | Master of Arts in English        |             13 |
+               :                               :                | Master of Arts in Modern         |             10 |
+               :                               :                : Languages                        :                :
+               :                               :                | Master of Arts in Science        |             11 |
+               :                               :                : Teaching                         :                :
+               | School of Natural Sciences    |              6 | Masters of Science in            |             13 |
+               :                               :                : Mathematics                      :                :
+               :                               :                | Doctorate of Science in          |             10 |
+               :                               :                : Mathematics                      :                :
+               :                               :                | Bachelor of Science in Astronomy |              5 |
+
+               ----
+               /school{name,count(program),/top(program,3){title,count(student)}}?campus='old'
+               SELECT "SCHOOL"."NAME",
+                      COALESCE("PROGRAM"."count", 0),
+                      "SCHOOL"."CODE"
+               FROM "SCHOOL"
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "PROGRAM"."SCHOOL_CODE"
+                                     FROM "PROGRAM"
+                                     GROUP BY "PROGRAM"."SCHOOL_CODE") "PROGRAM"
+                                    ON ("SCHOOL"."CODE" = "PROGRAM"."SCHOOL_CODE")
+               WHERE ("SCHOOL"."CAMPUS" = 'old')
+               ORDER BY 3 ASC
+
+                 SELECT "PROGRAM"."TITLE",
+                        COALESCE("STUDENT"."count", 0),
+                        "SCHOOL"."CODE"
+                 FROM "SCHOOL"
+                      INNER JOIN (SELECT "PROGRAM"."TITLE",
+                                         "PROGRAM"."SCHOOL_CODE",
+                                         "PROGRAM"."CODE"
+                                  FROM (SELECT "PROGRAM"."TITLE",
+                                               "PROGRAM"."SCHOOL_CODE",
+                                               "PROGRAM"."CODE",
+                                               ROW_NUMBER() OVER (PARTITION BY "PROGRAM"."SCHOOL_CODE" ORDER BY "PROGRAM"."SCHOOL_CODE" ASC, "PROGRAM"."CODE" ASC) AS "top"
+                                        FROM "PROGRAM") "PROGRAM"
+                                  WHERE ("PROGRAM"."top" >= 1)
+                                        AND ("PROGRAM"."top" < 4)) "PROGRAM"
+                                 ON ("SCHOOL"."CODE" = "PROGRAM"."SCHOOL_CODE")
+                      LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                              "STUDENT"."SCHOOL_CODE",
+                                              "STUDENT"."PROGRAM_CODE"
+                                       FROM "STUDENT"
+                                       GROUP BY "STUDENT"."SCHOOL_CODE", "STUDENT"."PROGRAM_CODE") "STUDENT"
+                                      ON (("PROGRAM"."SCHOOL_CODE" = "STUDENT"."SCHOOL_CODE") AND ("PROGRAM"."CODE" = "STUDENT"."PROGRAM_CODE"))
+                 WHERE ("SCHOOL"."CAMPUS" = 'old')
+                 ORDER BY 3 ASC, "PROGRAM"."SCHOOL_CODE" ASC, "PROGRAM"."CODE" ASC
+          - uri: /school{name, count(program), /top(program,3,2){title, count(student)}}?campus='old'
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school                                                                                             |
+               +-------------------------------+----------------+---------------------------------------------------+
+               |                               |                | top(program,3,2)                                  |
+               |                               |                +----------------------------------+----------------+
+               | name                          | count(program) | title                            | count(student) |
+              -+-------------------------------+----------------+----------------------------------+----------------+-
+               | School of Art & Design        |              3 | Bachelor of Arts in Studio Art   |             26 |
+               | College of Education          |              7 | Master of Arts in Literacy       |             15 |
+               :                               :                : Education                        :                :
+               :                               :                | Master of Arts in Teaching       |             13 |
+               :                               :                | Certificate in Science Teaching  |              8 |
+               | School of Arts and Humanities |              9 | Master of Arts in Science        |             11 |
+               :                               :                : Teaching                         :                :
+               :                               :                | Science Writing                  |              5 |
+               :                               :                | Bachelor of Arts in English      |              8 |
+               | School of Natural Sciences    |              6 | Bachelor of Science in Astronomy |              5 |
+               :                               :                | Bachelor of Science in Chemistry |             11 |
+               :                               :                | Bachelor of Science in           |             13 |
+               :                               :                : Mathematics                      :                :
+
+               ----
+               /school{name,count(program),/top(program,3,2){title,count(student)}}?campus='old'
+               SELECT "SCHOOL"."NAME",
+                      COALESCE("PROGRAM"."count", 0),
+                      "SCHOOL"."CODE"
+               FROM "SCHOOL"
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "PROGRAM"."SCHOOL_CODE"
+                                     FROM "PROGRAM"
+                                     GROUP BY "PROGRAM"."SCHOOL_CODE") "PROGRAM"
+                                    ON ("SCHOOL"."CODE" = "PROGRAM"."SCHOOL_CODE")
+               WHERE ("SCHOOL"."CAMPUS" = 'old')
+               ORDER BY 3 ASC
+
+                 SELECT "PROGRAM"."TITLE",
+                        COALESCE("STUDENT"."count", 0),
+                        "SCHOOL"."CODE"
+                 FROM "SCHOOL"
+                      INNER JOIN (SELECT "PROGRAM"."TITLE",
+                                         "PROGRAM"."SCHOOL_CODE",
+                                         "PROGRAM"."CODE"
+                                  FROM (SELECT "PROGRAM"."TITLE",
+                                               "PROGRAM"."SCHOOL_CODE",
+                                               "PROGRAM"."CODE",
+                                               ROW_NUMBER() OVER (PARTITION BY "PROGRAM"."SCHOOL_CODE" ORDER BY "PROGRAM"."SCHOOL_CODE" ASC, "PROGRAM"."CODE" ASC) AS "top"
+                                        FROM "PROGRAM") "PROGRAM"
+                                  WHERE ("PROGRAM"."top" >= 3)
+                                        AND ("PROGRAM"."top" < 6)) "PROGRAM"
+                                 ON ("SCHOOL"."CODE" = "PROGRAM"."SCHOOL_CODE")
+                      LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                              "STUDENT"."SCHOOL_CODE",
+                                              "STUDENT"."PROGRAM_CODE"
+                                       FROM "STUDENT"
+                                       GROUP BY "STUDENT"."SCHOOL_CODE", "STUDENT"."PROGRAM_CODE") "STUDENT"
+                                      ON (("PROGRAM"."SCHOOL_CODE" = "STUDENT"."SCHOOL_CODE") AND ("PROGRAM"."CODE" = "STUDENT"."PROGRAM_CODE"))
+                 WHERE ("SCHOOL"."CAMPUS" = 'old')
+                 ORDER BY 3 ASC, "PROGRAM"."SCHOOL_CODE" ASC, "PROGRAM"."CODE" ASC
+          - uri: /school^campus{*, top(^)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school^campus                                   |
+               +--------+----------------------------------------+
+               |        | top(^)                                 |
+               |        +------+------------------------+--------+
+               | campus | code | name                   | campus |
+              -+--------+------+------------------------+--------+-
+               | north  | eng  | School of Engineering  | north  |
+               | old    | art  | School of Art & Design | old    |
+               | south  | bus  | School of Business     | south  |
+
+               ----
+               /school^campus{*,top(^)}
+               SELECT "SCHOOL_1"."CAMPUS",
+                      "SCHOOL_2"."CODE",
+                      "SCHOOL_2"."NAME",
+                      "SCHOOL_2"."CAMPUS"
+               FROM (SELECT "SCHOOL"."CAMPUS"
+                     FROM "SCHOOL"
+                     WHERE ("SCHOOL"."CAMPUS" IS NOT NULL)
+                     GROUP BY "SCHOOL"."CAMPUS") "SCHOOL_1"
+                    INNER JOIN (SELECT "SCHOOL"."CODE",
+                                       "SCHOOL"."NAME",
+                                       "SCHOOL"."CAMPUS"
+                                FROM (SELECT "SCHOOL"."CODE",
+                                             "SCHOOL"."NAME",
+                                             "SCHOOL"."CAMPUS",
+                                             ROW_NUMBER() OVER (PARTITION BY "SCHOOL"."CAMPUS" ORDER BY "SCHOOL"."CODE" ASC) AS "top"
+                                      FROM "SCHOOL") "SCHOOL"
+                                WHERE ("SCHOOL"."top" >= 1)
+                                      AND ("SCHOOL"."top" < 2)) "SCHOOL_2"
+                               ON ("SCHOOL_1"."CAMPUS" = "SCHOOL_2"."CAMPUS")
+               ORDER BY 1 ASC NULLS FIRST
+          - uri: /class^{year, season} {*, /top(^.sort(count(enrollment)-),3).course{title}}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | class^{year,season}                              |
+               +------+--------+----------------------------------+
+               |      |        | course                           |
+               |      |        +----------------------------------+
+               | year | season | title                            |
+              -+------+--------+----------------------------------+-
+               | 2007 | fall   | College Algebra I                |
+               :      :        | History of Art Criticism I       |
+               :      :        | Language Basics                  |
+               | 2007 | spring | College Algebra II               |
+               :      :        | Introduction to Education        |
+               :      :        | Islam History                    |
+               | 2008 | fall   | College Algebra I                |
+               :      :        | Art in Therapy                   |
+               :      :        | History of Art Criticism I       |
+               | 2008 | spring | College Algebra II               |
+               :      :        | History of Computing             |
+               :      :        | Asian Architecture               |
+               | 2009 | fall   | Museum and Gallery Management    |
+               :      :        | Introduction to Biomedical       |
+               :      :        : Engineering                      :
+               :      :        | Bioengineering Seminar           |
+               | 2009 | spring | Introduction to Education        |
+               :      :        | Historical Perspective on the    |
+               :      :        : Constitution                     :
+               :      :        | College Algebra II               |
+               | 2010 | fall   | History of Art Criticism I       |
+               :      :        | Arts of Asia                     |
+               :      :        | Contemporary Latin American Art  |
+               | 2010 | spring | Practical Bookkeeping            |
+               :      :        | Asian Architecture               |
+               :      :        | Materials and Construction in    |
+               :      :        : European Art                     :
+
+               ----
+               /class^{year,season}{*,/top(^.sort(count(enrollment)-),3).course{title}}
+               SELECT "CLASS"."YEAR",
+                      "CLASS"."SEASON"
+               FROM "CLASS"
+               WHERE ("CLASS"."YEAR" IS NOT NULL)
+                     AND ("CLASS"."SEASON" IS NOT NULL)
+               GROUP BY "CLASS"."YEAR", "CLASS"."SEASON"
+               ORDER BY 1 ASC, 2 ASC
+
+                 SELECT "COURSE"."TITLE",
+                        "CLASS_1"."YEAR",
+                        "CLASS_1"."SEASON"
+                 FROM (SELECT "CLASS"."YEAR",
+                              "CLASS"."SEASON"
+                       FROM "CLASS"
+                       WHERE ("CLASS"."YEAR" IS NOT NULL)
+                             AND ("CLASS"."SEASON" IS NOT NULL)
+                       GROUP BY "CLASS"."YEAR", "CLASS"."SEASON") "CLASS_1"
+                      INNER JOIN (SELECT "CLASS"."DEPARTMENT_CODE",
+                                         "CLASS"."COURSE_NO",
+                                         "CLASS"."YEAR",
+                                         "CLASS"."SEASON",
+                                         "CLASS"."SECTION"
+                                  FROM (SELECT "CLASS"."DEPARTMENT_CODE",
+                                               "CLASS"."COURSE_NO",
+                                               "CLASS"."YEAR",
+                                               "CLASS"."SEASON",
+                                               "CLASS"."SECTION",
+                                               ROW_NUMBER() OVER (PARTITION BY "CLASS"."YEAR", "CLASS"."SEASON" ORDER BY COALESCE("ENROLLMENT"."count", 0) DESC, "CLASS"."DEPARTMENT_CODE" ASC, "CLASS"."COURSE_NO" ASC, "CLASS"."YEAR" ASC, "CLASS"."SEASON" ASC, "CLASS"."SECTION" ASC) AS "top"
+                                        FROM "CLASS"
+                                             LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                                                     "ENROLLMENT"."CLASS_SEQ"
+                                                              FROM "ENROLLMENT"
+                                                              GROUP BY "ENROLLMENT"."CLASS_SEQ") "ENROLLMENT"
+                                                             ON ("CLASS"."CLASS_SEQ" = "ENROLLMENT"."CLASS_SEQ")) "CLASS"
+                                  WHERE ("CLASS"."top" >= 1)
+                                        AND ("CLASS"."top" < 4)) "CLASS_2"
+                                 ON (("CLASS_1"."YEAR" = "CLASS_2"."YEAR") AND ("CLASS_1"."SEASON" = "CLASS_2"."SEASON"))
+                      INNER JOIN "COURSE"
+                                 ON (("CLASS_2"."DEPARTMENT_CODE" = "COURSE"."DEPARTMENT_CODE") AND ("CLASS_2"."COURSE_NO" = "COURSE"."NO"))
+                      INNER JOIN (SELECT COALESCE("CLASS"."count", 0) AS "count",
+                                         "CLASS"."DEPARTMENT_CODE",
+                                         "CLASS"."COURSE_NO",
+                                         "CLASS"."YEAR",
+                                         "CLASS"."SEASON",
+                                         "CLASS"."SECTION"
+                                  FROM (SELECT "ENROLLMENT"."count",
+                                               "CLASS"."DEPARTMENT_CODE",
+                                               "CLASS"."COURSE_NO",
+                                               "CLASS"."YEAR",
+                                               "CLASS"."SEASON",
+                                               "CLASS"."SECTION",
+                                               ROW_NUMBER() OVER (PARTITION BY "CLASS"."YEAR", "CLASS"."SEASON" ORDER BY COALESCE("ENROLLMENT"."count", 0) DESC, "CLASS"."DEPARTMENT_CODE" ASC, "CLASS"."COURSE_NO" ASC, "CLASS"."YEAR" ASC, "CLASS"."SEASON" ASC, "CLASS"."SECTION" ASC) AS "top"
+                                        FROM "CLASS"
+                                             LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                                                     "ENROLLMENT"."CLASS_SEQ"
+                                                              FROM "ENROLLMENT"
+                                                              GROUP BY "ENROLLMENT"."CLASS_SEQ") "ENROLLMENT"
+                                                             ON ("CLASS"."CLASS_SEQ" = "ENROLLMENT"."CLASS_SEQ")) "CLASS"
+                                  WHERE ("CLASS"."top" >= 1)
+                                        AND ("CLASS"."top" < 4)) "CLASS_3"
+                                 ON (("CLASS_2"."DEPARTMENT_CODE" = "CLASS_3"."DEPARTMENT_CODE") AND ("CLASS_2"."COURSE_NO" = "CLASS_3"."COURSE_NO") AND ("CLASS_2"."YEAR" = "CLASS_3"."YEAR") AND ("CLASS_2"."SEASON" = "CLASS_3"."SEASON") AND ("CLASS_2"."SECTION" = "CLASS_3"."SECTION"))
+                 ORDER BY 2 ASC, 3 ASC, "CLASS_3"."count" DESC, "CLASS_2"."DEPARTMENT_CODE" ASC, "CLASS_2"."COURSE_NO" ASC, "CLASS_2"."YEAR" ASC, "CLASS_2"."SEASON" ASC, "CLASS_2"."SECTION" ASC
         - id: table-expressions
           tests:
           - uri: /(school?code='art').department

test/output/pgsql.yaml

                     CROSS JOIN "ad"."program"
                ORDER BY "school"."code" ASC, "department"."code" ASC, 1 ASC, 2 ASC
                LIMIT 1
+        - id: top
+          tests:
+          - uri: /school :top
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school:top                             |
+               +------+------------------------+--------+
+               | code | name                   | campus |
+              -+------+------------------------+--------+-
+               | art  | School of Art & Design | old    |
+
+               ----
+               /school:top
+               SELECT "school"."code",
+                      "school"."name",
+                      "school"."campus"
+               FROM (SELECT "school"."code",
+                            "school"."name",
+                            "school"."campus"
+                     FROM "ad"."school"
+                     ORDER BY 1 ASC
+                     LIMIT 1) AS "school"
+               ORDER BY 1 ASC
+          - uri: /school :top(3)
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school:top(3)                          |
+               +------+------------------------+--------+
+               | code | name                   | campus |
+              -+------+------------------------+--------+-
+               | art  | School of Art & Design | old    |
+               | bus  | School of Business     | south  |
+               | edu  | College of Education   | old    |
+
+               ----
+               /school:top(3)
+               SELECT "school"."code",
+                      "school"."name",
+                      "school"."campus"
+               FROM (SELECT "school"."code",
+                            "school"."name",
+                            "school"."campus"
+                     FROM "ad"."school"
+                     ORDER BY 1 ASC
+                     LIMIT 3) AS "school"
+               ORDER BY 1 ASC
+          - uri: /school :top(3,2)
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school:top(3,2)                               |
+               +------+-------------------------------+--------+
+               | code | name                          | campus |
+              -+------+-------------------------------+--------+-
+               | edu  | College of Education          | old    |
+               | eng  | School of Engineering         | north  |
+               | la   | School of Arts and Humanities | old    |
+
+               ----
+               /school:top(3,2)
+               SELECT "school"."code",
+                      "school"."name",
+                      "school"."campus"
+               FROM (SELECT "school"."code",
+                            "school"."name",
+                            "school"."campus"
+                     FROM "ad"."school"
+                     ORDER BY 1 ASC
+                     LIMIT 3
+                     OFFSET 2) AS "school"
+               ORDER BY 1 ASC
+          - uri: /top(school.sort(count(department)-)){code, count(department)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | top(school.sort(count(department)-)) |
+               +------------+-------------------------+
+               | code       | count(department)       |
+              -+------------+-------------------------+-
+               | la         |                       6 |
+
+               ----
+               /top(school.sort(count(department)-)){code,count(department)}
+               SELECT "school_1"."code",
+                      COALESCE("department"."count", 0)
+               FROM (SELECT "school"."code"
+                     FROM "ad"."school"
+                          LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                                  "department"."school_code"
+                                           FROM "ad"."department"
+                                           GROUP BY 2) AS "department"
+                                          ON ("school"."code" = "department"."school_code")
+                     ORDER BY COALESCE("department"."count", 0) DESC, 1 ASC
+                     LIMIT 1) AS "school_1"
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                            "department"."school_code"
+                                     FROM "ad"."department"
+                                     GROUP BY 2) AS "department"
+                                    ON ("school_1"."code" = "department"."school_code")
+                    INNER JOIN (SELECT COALESCE("department"."count", 0) AS "count",
+                                       "school"."code"
+                                FROM "ad"."school"
+                                     LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                                             "department"."school_code"
+                                                      FROM "ad"."department"
+                                                      GROUP BY 2) AS "department"
+                                                     ON ("school"."code" = "department"."school_code")
+                                ORDER BY 1 DESC, 2 ASC
+                                LIMIT 1) AS "school_2"
+                               ON ("school_1"."code" = "school_2"."code")
+               ORDER BY "school_2"."count" DESC, 1 ASC
+          - uri: /top(school.sort(count(department)-),3){code, count(department)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | top(school.sort(count(department)-),3) |
+               +-------------+--------------------------+
+               | code        | count(department)        |
+              -+-------------+--------------------------+-
+               | la          |                        6 |
+               | eng         |                        4 |
+               | mus         |                        4 |
+
+               ----
+               /top(school.sort(count(department)-),3){code,count(department)}
+               SELECT "school_1"."code",
+                      COALESCE("department"."count", 0)
+               FROM (SELECT "school"."code"
+                     FROM "ad"."school"
+                          LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                                  "department"."school_code"
+                                           FROM "ad"."department"
+                                           GROUP BY 2) AS "department"
+                                          ON ("school"."code" = "department"."school_code")
+                     ORDER BY COALESCE("department"."count", 0) DESC, 1 ASC
+                     LIMIT 3) AS "school_1"
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                            "department"."school_code"
+                                     FROM "ad"."department"
+                                     GROUP BY 2) AS "department"
+                                    ON ("school_1"."code" = "department"."school_code")
+                    INNER JOIN (SELECT COALESCE("department"."count", 0) AS "count",
+                                       "school"."code"
+                                FROM "ad"."school"
+                                     LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                                             "department"."school_code"
+                                                      FROM "ad"."department"
+                                                      GROUP BY 2) AS "department"
+                                                     ON ("school"."code" = "department"."school_code")
+                                ORDER BY 1 DESC, 2 ASC
+                                LIMIT 3) AS "school_2"
+                               ON ("school_1"."code" = "school_2"."code")
+               ORDER BY "school_2"."count" DESC, 1 ASC
+          - uri: /top(school.sort(count(department)-),3,2){code, count(department)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | top(school.sort(count(department)-),3,2) |
+               +--------------+---------------------------+
+               | code         | count(department)         |
+              -+--------------+---------------------------+-
+               | mus          |                         4 |
+               | ns           |                         4 |
+               | bus          |                         3 |
+
+               ----
+               /top(school.sort(count(department)-),3,2){code,count(department)}
+               SELECT "school_1"."code",
+                      COALESCE("department"."count", 0)
+               FROM (SELECT "school"."code"
+                     FROM "ad"."school"
+                          LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                                  "department"."school_code"
+                                           FROM "ad"."department"
+                                           GROUP BY 2) AS "department"
+                                          ON ("school"."code" = "department"."school_code")
+                     ORDER BY COALESCE("department"."count", 0) DESC, 1 ASC
+                     LIMIT 3
+                     OFFSET 2) AS "school_1"
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                            "department"."school_code"
+                                     FROM "ad"."department"
+                                     GROUP BY 2) AS "department"
+                                    ON ("school_1"."code" = "department"."school_code")
+                    INNER JOIN (SELECT COALESCE("department"."count", 0) AS "count",
+                                       "school"."code"
+                                FROM "ad"."school"
+                                     LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                                             "department"."school_code"
+                                                      FROM "ad"."department"
+                                                      GROUP BY 2) AS "department"
+                                                     ON ("school"."code" = "department"."school_code")
+                                ORDER BY 1 DESC, 2 ASC
+                                LIMIT 3
+                                OFFSET 2) AS "school_2"
+                               ON ("school_1"."code" = "school_2"."code")
+               ORDER BY "school_2"."count" DESC, 1 ASC
+          - uri: /school{name, count(program), top(program){title, count(student)}}?campus='old'
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school                                                                                             |
+               +-------------------------------+----------------+---------------------------------------------------+
+               |                               |                | top(program)                                      |
+               |                               |                +----------------------------------+----------------+
+               | name                          | count(program) | title                            | count(student) |
+              -+-------------------------------+----------------+----------------------------------+----------------+-
+               | School of Art & Design        |              3 | Post Baccalaureate in Art        |             16 |
+               :                               :                : History                          :                :
+               | College of Education          |              7 | Master of Arts in Education      |             10 |
+               :                               :                : Leadership                       :                :
+               | School of Arts and Humanities |              9 | Master of Arts in English        |             13 |
+               | School of Natural Sciences    |              6 | Masters of Science in            |             13 |
+               :                               :                : Mathematics                      :                :
+
+               ----
+               /school{name,count(program),top(program){title,count(student)}}?campus='old'
+               SELECT "school"."name",
+                      COALESCE("program_1"."count", 0),
+                      "program_2"."program",
+                      "program_2"."title",
+                      "program_2"."count"
+               FROM "ad"."school"
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                            "program"."school_code"
+                                     FROM "ad"."program"
+                                     GROUP BY 2) AS "program_1"
+                                    ON ("school"."code" = "program_1"."school_code")
+                    LEFT OUTER JOIN (SELECT TRUE AS "program",
+                                            "program"."title",
+                                            COALESCE("student"."count", 0) AS "count",
+                                            "program"."school_code"
+                                     FROM (SELECT "program"."title",
+                                                  "program"."school_code",
+                                                  "program"."code",
+                                                  ROW_NUMBER() OVER (PARTITION BY "program"."school_code" ORDER BY "program"."school_code" ASC, "program"."code" ASC) AS "top"
+                                           FROM "ad"."program") AS "program"
+                                          LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                                                  "student"."school_code",
+                                                                  "student"."program_code"
+                                                           FROM "ed"."student"
+                                                           GROUP BY 2, 3) AS "student"
+                                                          ON (("program"."school_code" = "student"."school_code") AND ("program"."code" = "student"."program_code"))
+                                     WHERE ("program"."top" >= 1)
+                                           AND ("program"."top" < 2)) AS "program_2"
+                                    ON ("school"."code" = "program_2"."school_code")
+               WHERE ("school"."campus" = 'old')
+               ORDER BY "school"."code" ASC
+          - uri: /school{name, count(program), /top(program,3){title, count(student)}}?campus='old'
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school                                                                                             |
+               +-------------------------------+----------------+---------------------------------------------------+
+               |                               |                | top(program,3)                                    |
+               |                               |                +----------------------------------+----------------+
+               | name                          | count(program) | title                            | count(student) |
+              -+-------------------------------+----------------+----------------------------------+----------------+-
+               | School of Art & Design        |              3 | Post Baccalaureate in Art        |             16 |
+               :                               :                : History                          :                :
+               :                               :                | Bachelor of Arts in Art History  |             20 |
+               :                               :                | Bachelor of Arts in Studio Art   |             26 |
+               | College of Education          |              7 | Master of Arts in Education      |             10 |
+               :                               :                : Leadership                       :                :
+               :                               :                | M.S. in Education                |             11 |
+               :                               :                | Master of Arts in Literacy       |             15 |
+               :                               :                : Education                        :                :
+               | School of Arts and Humanities |              9 | Master of Arts in English        |             13 |
+               :                               :                | Master of Arts in Modern         |             10 |
+               :                               :                : Languages                        :                :
+               :                               :                | Master of Arts in Science        |             11 |
+               :                               :                : Teaching                         :                :
+               | School of Natural Sciences    |              6 | Masters of Science in            |             13 |
+               :                               :                : Mathematics                      :                :
+               :                               :                | Doctorate of Science in          |             10 |
+               :                               :                : Mathematics                      :                :
+               :                               :                | Bachelor of Science in Astronomy |              5 |
+
+               ----
+               /school{name,count(program),/top(program,3){title,count(student)}}?campus='old'
+               SELECT "school"."name",
+                      COALESCE("program"."count", 0),
+                      "school"."code"
+               FROM "ad"."school"
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                            "program"."school_code"
+                                     FROM "ad"."program"
+                                     GROUP BY 2) AS "program"
+                                    ON ("school"."code" = "program"."school_code")
+               WHERE ("school"."campus" = 'old')
+               ORDER BY 3 ASC
+
+                 SELECT "program"."title",
+                        COALESCE("student"."count", 0),
+                        "school"."code"
+                 FROM "ad"."school"
+                      INNER JOIN (SELECT "program"."title",
+                                         "program"."school_code",
+                                         "program"."code"
+                                  FROM (SELECT "program"."title",
+                                               "program"."school_code",
+                                               "program"."code",
+                                               ROW_NUMBER() OVER (PARTITION BY "program"."school_code" ORDER BY "program"."school_code" ASC, "program"."code" ASC) AS "top"
+                                        FROM "ad"."program") AS "program"
+                                  WHERE ("program"."top" >= 1)
+                                        AND ("program"."top" < 4)) AS "program"
+                                 ON ("school"."code" = "program"."school_code")
+                      LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                              "student"."school_code",
+                                              "student"."program_code"
+                                       FROM "ed"."student"
+                                       GROUP BY 2, 3) AS "student"
+                                      ON (("program"."school_code" = "student"."school_code") AND ("program"."code" = "student"."program_code"))
+                 WHERE ("school"."campus" = 'old')
+                 ORDER BY 3 ASC, "program"."school_code" ASC, "program"."code" ASC
+          - uri: /school{name, count(program), /top(program,3,2){title, count(student)}}?campus='old'
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school                                                                                             |
+               +-------------------------------+----------------+---------------------------------------------------+
+               |                               |                | top(program,3,2)                                  |
+               |                               |                +----------------------------------+----------------+
+               | name                          | count(program) | title                            | count(student) |
+              -+-------------------------------+----------------+----------------------------------+----------------+-
+               | School of Art & Design        |              3 | Bachelor of Arts in Studio Art   |             26 |
+               | College of Education          |              7 | Master of Arts in Literacy       |             15 |
+               :                               :                : Education                        :                :
+               :                               :                | Master of Arts in Teaching       |             13 |
+               :                               :                | Certificate in Science Teaching  |              8 |
+               | School of Arts and Humanities |              9 | Master of Arts in Science        |             11 |
+               :                               :                : Teaching                         :                :
+               :                               :                | Science Writing                  |              5 |
+               :                               :                | Bachelor of Arts in English      |              8 |
+               | School of Natural Sciences    |              6 | Bachelor of Science in Astronomy |              5 |
+               :                               :                | Bachelor of Science in Chemistry |             11 |
+               :                               :                | Bachelor of Science in           |             13 |
+               :                               :                : Mathematics                      :                :
+
+               ----
+               /school{name,count(program),/top(program,3,2){title,count(student)}}?campus='old'
+               SELECT "school"."name",
+                      COALESCE("program"."count", 0),
+                      "school"."code"
+               FROM "ad"."school"
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                            "program"."school_code"
+                                     FROM "ad"."program"
+                                     GROUP BY 2) AS "program"
+                                    ON ("school"."code" = "program"."school_code")
+               WHERE ("school"."campus" = 'old')
+               ORDER BY 3 ASC
+
+                 SELECT "program"."title",
+                        COALESCE("student"."count", 0),
+                        "school"."code"
+                 FROM "ad"."school"
+                      INNER JOIN (SELECT "program"."title",
+                                         "program"."school_code",
+                                         "program"."code"
+                                  FROM (SELECT "program"."title",
+                                               "program"."school_code",
+                                               "program"."code",
+                                               ROW_NUMBER() OVER (PARTITION BY "program"."school_code" ORDER BY "program"."school_code" ASC, "program"."code" ASC) AS "top"
+                                        FROM "ad"."program") AS "program"
+                                  WHERE ("program"."top" >= 3)
+                                        AND ("program"."top" < 6)) AS "program"
+                                 ON ("school"."code" = "program"."school_code")
+                      LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                              "student"."school_code",
+                                              "student"."program_code"
+                                       FROM "ed"."student"
+                                       GROUP BY 2, 3) AS "student"
+                                      ON (("program"."school_code" = "student"."school_code") AND ("program"."code" = "student"."program_code"))
+                 WHERE ("school"."campus" = 'old')
+                 ORDER BY 3 ASC, "program"."school_code" ASC, "program"."code" ASC
+          - uri: /school^campus{*, top(^)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | school^campus                                   |
+               +--------+----------------------------------------+
+               |        | top(^)                                 |
+               |        +------+------------------------+--------+
+               | campus | code | name                   | campus |
+              -+--------+------+------------------------+--------+-
+               | north  | eng  | School of Engineering  | north  |
+               | old    | art  | School of Art & Design | old    |
+               | south  | bus  | School of Business     | south  |
+
+               ----
+               /school^campus{*,top(^)}
+               SELECT "school_1"."campus",
+                      "school_2"."code",
+                      "school_2"."name",
+                      "school_2"."campus"
+               FROM (SELECT "school"."campus"
+                     FROM "ad"."school"
+                     WHERE ("school"."campus" IS NOT NULL)
+                     GROUP BY 1) AS "school_1"
+                    INNER JOIN (SELECT "school"."code",
+                                       "school"."name",
+                                       "school"."campus"
+                                FROM (SELECT "school"."code",
+                                             "school"."name",
+                                             "school"."campus",
+                                             ROW_NUMBER() OVER (PARTITION BY "school"."campus" ORDER BY "school"."code" ASC) AS "top"
+                                      FROM "ad"."school") AS "school"
+                                WHERE ("school"."top" >= 1)
+                                      AND ("school"."top" < 2)) AS "school_2"
+                               ON ("school_1"."campus" = "school_2"."campus")
+               ORDER BY 1 ASC NULLS FIRST
+          - uri: /class^{year, season} {*, /top(^.sort(count(enrollment)-),3).course{title}}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            - [Vary, Accept]
+            body: |2
+               | class^{year,season}                              |
+               +------+--------+----------------------------------+
+               |      |        | course                           |
+               |      |        +----------------------------------+
+               | year | season | title                            |
+              -+------+--------+----------------------------------+-
+               | 2007 | fall   | College Algebra I                |
+               :      :        | History of Art Criticism I       |
+               :      :        | Language Basics                  |
+               | 2007 | spring | College Algebra II               |
+               :      :        | Introduction to Education        |
+               :      :        | Islam History                    |
+               | 2008 | fall   | College Algebra I                |
+               :      :        | Art in Therapy                   |
+               :      :        | History of Art Criticism I       |
+               | 2008 | spring | College Algebra II               |
+               :      :        | History of Computing             |
+               :      :        | Asian Architecture               |
+               | 2009 | fall   | Museum and Gallery Management    |
+               :      :        | Introduction to Biomedical       |
+               :      :        : Engineering                      :
+               :      :        | Bioengineering Seminar           |
+               | 2009 | spring | Introduction to Education        |
+               :      :        | Historical Perspective on the    |
+               :      :        : Constitution                     :
+               :      :        | College Algebra II               |
+               | 2010 | fall   | History of Art Criticism I       |
+               :      :        | Arts of Asia                     |
+               :      :        | Contemporary Latin American Art  |
+               | 2010 | spring | Practical Bookkeeping            |
+               :      :        | Asian Architecture               |
+               :      :        | Materials and Construction in    |
+               :      :        : European Art                     :
+
+               ----
+               /class^{year,season}{*,/top(^.sort(count(enrollment)-),3).course{title}}
+               SELECT "class"."year",
+                      "class"."season"
+               FROM "cd"."class"
+               WHERE ("class"."year" IS NOT NULL)
+                     AND ("class"."season" IS NOT NULL)
+               GROUP BY 1, 2
+               ORDER BY 1 ASC, 2 ASC
+
+                 SELECT "course"."title",
+                        "class_1"."year",
+                        "class_1"."season"
+                 FROM (SELECT "class"."year",
+                              "class"."season"
+                       FROM "cd"."class"
+                       WHERE ("class"."year" IS NOT NULL)
+                             AND ("class"."season" IS NOT NULL)
+                       GROUP BY 1, 2) AS "class_1"
+                      INNER JOIN (SELECT "class"."department_code",
+                                         "class"."course_no",
+                                         "class"."year",
+                                         "class"."season",
+                                         "class"."section"
+                                  FROM (SELECT "class"."department_code",
+                                               "class"."course_no",
+                                               "class"."year",
+                                               "class"."season",
+                                               "class"."section",
+                                               ROW_NUMBER() OVER (PARTITION BY "class"."year", "class"."season" ORDER BY COALESCE("enrollment"."count", 0) DESC, "class"."department_code" ASC, "class"."course_no" ASC, "class"."year" ASC, "class"."season" ASC, "class"."section" ASC) AS "top"
+                                        FROM "cd"."class"
+                                             LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                                                     "enrollment"."class_seq"
+                                                              FROM "ed"."enrollment"
+                                                              GROUP BY 2) AS "enrollment"
+                                                             ON ("class"."class_seq" = "enrollment"."class_seq")) AS "class"
+                                  WHERE ("class"."top" >= 1)
+                                        AND ("class"."top" < 4)) AS "class_2"
+                                 ON (("class_1"."year" = "class_2"."year") AND ("class_1"."season" = "class_2"."season"))
+                      INNER JOIN "ad"."course"
+                                 ON (("class_2"."department_code" = "course"."department_code") AND ("class_2"."course_no" = "course"."no"))
+                      INNER JOIN (SELECT COALESCE("class"."count", 0) AS "count",
+                                         "class"."department_code",
+                                         "class"."course_no",
+                                         "class"."year",
+                                         "class"."season",
+                                         "class"."section"
+                                  FROM (SELECT "enrollment"."count",
+                                               "class"."department_code",
+                                               "class"."course_no",
+                                               "class"."year",
+                                               "class"."season",
+                                               "class"."section",
+                                               ROW_NUMBER() OVER (PARTITION BY "class"."year", "class"."season" ORDER BY COALESCE("enrollment"."count", 0) DESC, "class"."department_code" ASC, "class"."course_no" ASC, "class"."year" ASC, "class"."season" ASC, "class"."section" ASC) AS "top"
+                                        FROM "cd"."class"
+                                             LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                                                     "enrollment"."class_seq"
+                                                              FROM "ed"."enrollment"
+                                                              GROUP BY 2) AS "enrollment"
+                                                             ON ("class"."class_seq" = "enrollment"."class_seq")) AS "class"
+                                  WHERE ("class"."top" >= 1)
+                                        AND ("class"."top" < 4)) AS "class_3"
+                                 ON (("class_2"."department_code" = "class_3"."department_code") AND ("class_2"."course_no" = "class_3"."course_no") AND ("class_2"."year" = "class_3"."year") AND ("class_2"."season" = "class_3"."season") AND ("class_2"."section" = "class_3"."section"))
+                 ORDER BY 2 ASC, 3 ASC, "class_3"."count" DESC, "class_2"."department_code" ASC, "class_2"."course_no" ASC, "class_2"."year" ASC, "class_2"."season" ASC, "class_2"."section" ASC
         - id: table-expressions
           tests:
           - uri: /(school?code='art').department
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.