A single WITH clause may define more than one common table expression. These definitions must be separated by commas. The following example lists the department that has the smallest payroll and the department that has the largest number of employees.
WITH CountEmployees( DepartmentID, n ) AS ( SELECT DepartmentID, COUNT( * ) AS n FROM Employees GROUP BY DepartmentID ), DeptPayroll( DepartmentID, amt ) AS ( SELECT DepartmentID, SUM( Salary ) AS amt FROM Employees GROUP BY DepartmentID ) SELECT count.DepartmentID, count.n, pay.amt FROM CountEmployees AS count JOIN DeptPayroll AS pay ON count.DepartmentID = pay.DepartmentID WHERE count.n = ( SELECT MAX( n ) FROM CountEmployees ) OR pay.amt = ( SELECT MIN( amt ) FROM DeptPayroll ); |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |