Commits

Kirill Simonov  committed 973e49c

top(): top term now exports ordering codes.

  • Participants
  • Parent commits 3df9882

Comments (0)

Files changed (6)

File src/htsql/core/tr/compile.py

         # Ensure we can satisfy the joints.
         for joint in joints:
             units.append(joint.rop)
-        # Export any requested companion units.
-        for code in self.flow.companions:
+        # Export any requested companion units and other generated codes.
+        for code in codes:
             unit = CoveringUnit(code, self.backbone, code.binding)
             units.append(unit)
 

File test/output/mssql.yaml

 
                ----
                /top(school.sort(count(department)-)){code,count(department)}
-               SELECT [school_1].[code],
+               SELECT [school].[code],
                       COALESCE([department].[count], 0)
                FROM (SELECT TOP 1
-                            [school].[code]
+                            [school].[code],
+                            COALESCE([department].[count], 0) AS [count]
                      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]
+                     ORDER BY 2 DESC, 1 ASC) AS [school]
                     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
+                                    ON ([school].[code] = [department].[school_code])
+               ORDER BY [school].[count] DESC, 1 ASC
           - uri: /top(school.sort(count(department)-),3){code, count(department)}
             status: 200 OK
             headers:
 
                ----
                /top(school.sort(count(department)-),3){code,count(department)}
-               SELECT [school_1].[code],
+               SELECT [school].[code],
                       COALESCE([department].[count], 0)
                FROM (SELECT TOP 3
-                            [school].[code]
+                            [school].[code],
+                            COALESCE([department].[count], 0) AS [count]
                      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]
+                     ORDER BY 2 DESC, 1 ASC) AS [school]
                     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
+                                    ON ([school].[code] = [department].[school_code])
+               ORDER BY [school].[count] DESC, 1 ASC
           - uri: /top(school.sort(count(department)-),3,2){code, count(department)}
             status: 200 OK
             headers:
 
                ----
                /top(school.sort(count(department)-),3,2){code,count(department)}
-               SELECT [school_1].[code],
+               SELECT [school].[code],
                       COALESCE([department].[count], 0)
                FROM (SELECT [school].[code],
+                            [department].[count],
                             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]
+                                          ON ([school].[code] = [department].[school_code])) AS [school]
                     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
+                                    ON ([school].[code] = [department].[school_code])
+               WHERE ([school].[top] >= 3)
+                     AND ([school].[top] < 6)
+               ORDER BY COALESCE([school].[count], 0) DESC, 1 ASC
           - uri: /school{name, count(program), top(program){title, count(student)}}?campus='old'
             status: 200 OK
             headers:
                        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],
                                                               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
+                                        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]))
+                 ORDER BY 2 ASC, 3 ASC, [class_2].[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

File test/output/mysql.yaml

 
                ----
                /top(school.sort(count(department)-)){code,count(department)}
-               SELECT `school_1`.`code`,
+               SELECT `school`.`code`,
                       COALESCE(`department`.`count`, 0)
-               FROM (SELECT `school`.`code`
+               FROM (SELECT `school`.`code`,
+                            COALESCE(`department`.`count`, 0) AS `count`
                      FROM `school`
                           LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
                                                   `department`.`school_code`
                                            FROM `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`
+                     ORDER BY 2 DESC, 1 ASC
+                     LIMIT 1) AS `school`
                     LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
                                             `department`.`school_code`
                                      FROM `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 `school`
-                                     LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
-                                                             `department`.`school_code`
-                                                      FROM `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
+                                    ON (`school`.`code` = `department`.`school_code`)
+               ORDER BY `school`.`count` DESC, 1 ASC
           - uri: /top(school.sort(count(department)-),3){code, count(department)}
             status: 200 OK
             headers:
 
                ----
                /top(school.sort(count(department)-),3){code,count(department)}
-               SELECT `school_1`.`code`,
+               SELECT `school`.`code`,
                       COALESCE(`department`.`count`, 0)
-               FROM (SELECT `school`.`code`
+               FROM (SELECT `school`.`code`,
+                            COALESCE(`department`.`count`, 0) AS `count`
                      FROM `school`
                           LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
                                                   `department`.`school_code`
                                            FROM `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`
+                     ORDER BY 2 DESC, 1 ASC
+                     LIMIT 3) AS `school`
                     LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
                                             `department`.`school_code`
                                      FROM `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 `school`
-                                     LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
-                                                             `department`.`school_code`
-                                                      FROM `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
+                                    ON (`school`.`code` = `department`.`school_code`)
+               ORDER BY `school`.`count` DESC, 1 ASC
           - uri: /top(school.sort(count(department)-),3,2){code, count(department)}
             status: 200 OK
             headers:
 
                ----
                /top(school.sort(count(department)-),3,2){code,count(department)}
-               SELECT `school_1`.`code`,
+               SELECT `school`.`code`,
                       COALESCE(`department`.`count`, 0)
-               FROM (SELECT `school`.`code`
+               FROM (SELECT `school`.`code`,
+                            COALESCE(`department`.`count`, 0) AS `count`
                      FROM `school`
                           LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
                                                   `department`.`school_code`
                                            FROM `department`
                                            GROUP BY 2) AS `department`
                                           ON (`school`.`code` = `department`.`school_code`)
-                     ORDER BY COALESCE(`department`.`count`, 0) DESC, 1 ASC
+                     ORDER BY 2 DESC, 1 ASC
                      LIMIT 3
-                     OFFSET 2) AS `school_1`
+                     OFFSET 2) AS `school`
                     LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
                                             `department`.`school_code`
                                      FROM `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 `school`
-                                     LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
-                                                             `department`.`school_code`
-                                                      FROM `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
+                                    ON (`school`.`code` = `department`.`school_code`)
+               ORDER BY `school`.`count` DESC, 1 ASC
           - uri: /school{name, count(program), top(program){title, count(student)}}?campus='old'
             status: 200 OK
             headers:
                        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`,
+                      INNER JOIN (SELECT COALESCE(`class`.`count`, 0) AS `count`,
+                                         `class`.`department_code`,
                                          `class`.`course_no`,
                                          `class`.`year`,
                                          `class`.`season`,
                                          `class`.`section`
-                                  FROM (SELECT `class`.`department_code`,
+                                  FROM (SELECT `class`.`count`,
+                                               `class`.`department_code`,
                                                `class`.`course_no`,
                                                `class`.`year`,
                                                `class`.`season`,
                                                @`!htsql:15:row_number` := IF(((@`!htsql:15:partition:1` = `class`.`year`) AND (@`!htsql:15:partition:2` = `class`.`season`)), (@`!htsql:15:row_number` + 1), 1) AS `top_1`,
                                                @`!htsql:15:partition:1` := `class`.`year` AS `top_2`,
                                                @`!htsql:15:partition:2` := `class`.`season` AS `top_3`
-                                        FROM (SELECT `class`.`department_code`,
+                                        FROM (SELECT `enrollment`.`count`,
+                                                     `class`.`department_code`,
                                                      `class`.`course_no`,
                                                      `class`.`year`,
                                                      `class`.`season`,
                                                                FROM (SELECT @`!htsql:15:row_number` := NULL AS `top_1`,
                                                                             @`!htsql:15:partition:1` := NULL AS `top_2`,
                                                                             @`!htsql:15:partition:2` := NULL AS `top_3`) AS `!`) AS `!`
-                                              ORDER BY 3 ASC, 4 ASC, COALESCE(`enrollment`.`count`, 0) DESC, 1 ASC, 2 ASC, 5 ASC) AS `class`) AS `class`
+                                              ORDER BY 4 ASC, 5 ASC, COALESCE(`enrollment`.`count`, 0) DESC, 2 ASC, 3 ASC, 6 ASC) AS `class`) AS `class`
                                   WHERE (`class`.`top_1` >= 1)
                                         AND (`class`.`top_1` < 4)) AS `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 `class`.`count`,
-                                               `class`.`department_code`,
-                                               `class`.`course_no`,
-                                               `class`.`year`,
-                                               `class`.`season`,
-                                               `class`.`section`,
-                                               @`!htsql:41:row_number` := IF(((@`!htsql:41:partition:1` = `class`.`year`) AND (@`!htsql:41:partition:2` = `class`.`season`)), (@`!htsql:41:row_number` + 1), 1) AS `top_1`,
-                                               @`!htsql:41:partition:1` := `class`.`year` AS `top_2`,
-                                               @`!htsql:41:partition:2` := `class`.`season` AS `top_3`
-                                        FROM (SELECT `enrollment`.`count`,
-                                                     `class`.`department_code`,
-                                                     `class`.`course_no`,
-                                                     `class`.`year`,
-                                                     `class`.`season`,
-                                                     `class`.`section`
-                                              FROM `class`
-                                                   LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
-                                                                           `enrollment`.`class_seq`
-                                                                    FROM `enrollment`
-                                                                    GROUP BY 2) AS `enrollment`
-                                                                   ON (`class`.`class_seq` = `enrollment`.`class_seq`)
-                                                   CROSS JOIN (SELECT TRUE AS `!`
-                                                               FROM (SELECT @`!htsql:41:row_number` := NULL AS `top_1`,
-                                                                            @`!htsql:41:partition:1` := NULL AS `top_2`,
-                                                                            @`!htsql:41:partition:2` := NULL AS `top_3`) AS `!`) AS `!`
-                                              ORDER BY 4 ASC, 5 ASC, COALESCE(`enrollment`.`count`, 0) DESC, 2 ASC, 3 ASC, 6 ASC) AS `class`) AS `class`
-                                  WHERE (`class`.`top_1` >= 1)
-                                        AND (`class`.`top_1` < 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
+                 ORDER BY 2 ASC, 3 ASC, `class_2`.`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

File test/output/oracle.yaml

 
                ----
                /top(school.sort(count(department)-)){code,count(department)}
-               SELECT "SCHOOL_1"."CODE",
+               SELECT "SCHOOL"."CODE",
                       COALESCE("DEPARTMENT"."count", 0)
                FROM (SELECT "SCHOOL"."CODE",
+                            "SCHOOL"."count",
                             ROWNUM AS "top"
-                     FROM (SELECT "SCHOOL"."CODE"
+                     FROM (SELECT "SCHOOL"."CODE",
+                                  "DEPARTMENT"."count"
                            FROM "SCHOOL"
                                 LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
                                                         "DEPARTMENT"."SCHOOL_CODE"
                                                  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"
+                     WHERE (ROWNUM < 2)) "SCHOOL"
                     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
+                                    ON ("SCHOOL"."CODE" = "DEPARTMENT"."SCHOOL_CODE")
+               WHERE ("SCHOOL"."top" >= 1)
+               ORDER BY COALESCE("SCHOOL"."count", 0) DESC, 1 ASC
           - uri: /top(school.sort(count(department)-),3){code, count(department)}
             status: 200 OK
             headers:
 
                ----
                /top(school.sort(count(department)-),3){code,count(department)}
-               SELECT "SCHOOL_1"."CODE",
+               SELECT "SCHOOL"."CODE",
                       COALESCE("DEPARTMENT"."count", 0)
                FROM (SELECT "SCHOOL"."CODE",
+                            "SCHOOL"."count",
                             ROWNUM AS "top"
-                     FROM (SELECT "SCHOOL"."CODE"
+                     FROM (SELECT "SCHOOL"."CODE",
+                                  "DEPARTMENT"."count"
                            FROM "SCHOOL"
                                 LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
                                                         "DEPARTMENT"."SCHOOL_CODE"
                                                  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"
+                     WHERE (ROWNUM < 4)) "SCHOOL"
                     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
+                                    ON ("SCHOOL"."CODE" = "DEPARTMENT"."SCHOOL_CODE")
+               WHERE ("SCHOOL"."top" >= 1)
+               ORDER BY COALESCE("SCHOOL"."count", 0) DESC, 1 ASC
           - uri: /top(school.sort(count(department)-),3,2){code, count(department)}
             status: 200 OK
             headers:
 
                ----
                /top(school.sort(count(department)-),3,2){code,count(department)}
-               SELECT "SCHOOL_1"."CODE",
+               SELECT "SCHOOL"."CODE",
                       COALESCE("DEPARTMENT"."count", 0)
                FROM (SELECT "SCHOOL"."CODE",
+                            "SCHOOL"."count",
                             ROWNUM AS "top"
-                     FROM (SELECT "SCHOOL"."CODE"
+                     FROM (SELECT "SCHOOL"."CODE",
+                                  "DEPARTMENT"."count"
                            FROM "SCHOOL"
                                 LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
                                                         "DEPARTMENT"."SCHOOL_CODE"
                                                  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"
+                     WHERE (ROWNUM < 6)) "SCHOOL"
                     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
+                                    ON ("SCHOOL"."CODE" = "DEPARTMENT"."SCHOOL_CODE")
+               WHERE ("SCHOOL"."top" >= 3)
+               ORDER BY COALESCE("SCHOOL"."count", 0) DESC, 1 ASC
           - uri: /school{name, count(program), top(program){title, count(student)}}?campus='old'
             status: 200 OK
             headers:
                        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",
                                                               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
+                                        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"))
+                 ORDER BY 2 ASC, 3 ASC, "CLASS_2"."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

File test/output/pgsql.yaml

 
                ----
                /top(school.sort(count(department)-)){code,count(department)}
-               SELECT "school_1"."code",
+               SELECT "school"."code",
                       COALESCE("department"."count", 0)
-               FROM (SELECT "school"."code"
+               FROM (SELECT "school"."code",
+                            COALESCE("department"."count", 0) AS "count"
                      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"
+                     ORDER BY 2 DESC, 1 ASC
+                     LIMIT 1) AS "school"
                     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
+                                    ON ("school"."code" = "department"."school_code")
+               ORDER BY "school"."count" DESC, 1 ASC
           - uri: /top(school.sort(count(department)-),3){code, count(department)}
             status: 200 OK
             headers:
 
                ----
                /top(school.sort(count(department)-),3){code,count(department)}
-               SELECT "school_1"."code",
+               SELECT "school"."code",
                       COALESCE("department"."count", 0)
-               FROM (SELECT "school"."code"
+               FROM (SELECT "school"."code",
+                            COALESCE("department"."count", 0) AS "count"
                      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"
+                     ORDER BY 2 DESC, 1 ASC
+                     LIMIT 3) AS "school"
                     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
+                                    ON ("school"."code" = "department"."school_code")
+               ORDER BY "school"."count" DESC, 1 ASC
           - uri: /top(school.sort(count(department)-),3,2){code, count(department)}
             status: 200 OK
             headers:
 
                ----
                /top(school.sort(count(department)-),3,2){code,count(department)}
-               SELECT "school_1"."code",
+               SELECT "school"."code",
                       COALESCE("department"."count", 0)
-               FROM (SELECT "school"."code"
+               FROM (SELECT "school"."code",
+                            COALESCE("department"."count", 0) AS "count"
                      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
+                     ORDER BY 2 DESC, 1 ASC
                      LIMIT 3
-                     OFFSET 2) AS "school_1"
+                     OFFSET 2) AS "school"
                     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
+                                    ON ("school"."code" = "department"."school_code")
+               ORDER BY "school"."count" DESC, 1 ASC
           - uri: /school{name, count(program), top(program){title, count(student)}}?campus='old'
             status: 200 OK
             headers:
                        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",
                                                               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
+                                        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"))
+                 ORDER BY 2 ASC, 3 ASC, "class_2"."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

File test/output/sqlite.yaml

 
                ----
                /top(school.sort(count(department)-)){code,count(department)}
-               SELECT "school_1"."code",
+               SELECT "school"."code",
                       COALESCE("department"."count", 0)
-               FROM (SELECT "school"."code"
+               FROM (SELECT "school"."code",
+                            COALESCE("department"."count", 0) AS "count"
                      FROM "school"
                           LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
                                                   "department"."school_code"
                                            FROM "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"
+                     ORDER BY 2 DESC, 1 ASC
+                     LIMIT 1) AS "school"
                     LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
                                             "department"."school_code"
                                      FROM "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 "school"
-                                     LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
-                                                             "department"."school_code"
-                                                      FROM "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
+                                    ON ("school"."code" = "department"."school_code")
+               ORDER BY "school"."count" DESC, 1 ASC
           - uri: /top(school.sort(count(department)-),3){code, count(department)}
             status: 200 OK
             headers:
 
                ----
                /top(school.sort(count(department)-),3){code,count(department)}
-               SELECT "school_1"."code",
+               SELECT "school"."code",
                       COALESCE("department"."count", 0)
-               FROM (SELECT "school"."code"
+               FROM (SELECT "school"."code",
+                            COALESCE("department"."count", 0) AS "count"
                      FROM "school"
                           LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
                                                   "department"."school_code"
                                            FROM "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"
+                     ORDER BY 2 DESC, 1 ASC
+                     LIMIT 3) AS "school"
                     LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
                                             "department"."school_code"
                                      FROM "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 "school"
-                                     LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
-                                                             "department"."school_code"
-                                                      FROM "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
+                                    ON ("school"."code" = "department"."school_code")
+               ORDER BY "school"."count" DESC, 1 ASC
           - uri: /top(school.sort(count(department)-),3,2){code, count(department)}
             status: 200 OK
             headers:
 
                ----
                /top(school.sort(count(department)-),3,2){code,count(department)}
-               SELECT "school_1"."code",
+               SELECT "school"."code",
                       COALESCE("department"."count", 0)
-               FROM (SELECT "school"."code"
+               FROM (SELECT "school"."code",
+                            COALESCE("department"."count", 0) AS "count"
                      FROM "school"
                           LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
                                                   "department"."school_code"
                                            FROM "department"
                                            GROUP BY 2) AS "department"
                                           ON ("school"."code" = "department"."school_code")
-                     ORDER BY COALESCE("department"."count", 0) DESC, 1 ASC
+                     ORDER BY 2 DESC, 1 ASC
                      LIMIT 3
-                     OFFSET 2) AS "school_1"
+                     OFFSET 2) AS "school"
                     LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
                                             "department"."school_code"
                                      FROM "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 "school"
-                                     LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
-                                                             "department"."school_code"
-                                                      FROM "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
+                                    ON ("school"."code" = "department"."school_code")
+               ORDER BY "school"."count" DESC, 1 ASC
           - uri: /school{name, count(program), top(program){title, count(student)}}?campus='old'
             status: 200 OK
             headers: