Supposed that we are creating a report of the top 3 highest salary employees for every department (check the Oracle demo table : SCOTT.EMP). If the table is stored in Oracle database, we could generate the report with one Oracle SQL query as following.
SELECT * FROM (
SELECT DEPTNO, EMPNO, ENAME, SAL,
RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) RNK
FROM EMP ) WHERE RNK <= 3
But if we store this employee table in MySQL or SQLLite database, generating the top n salary employee report will be very complex or difficult. It's hard for us to know different SQL syntax on different database systems. With the feature of the WebChart, you can generate the report with very simple SQL query.
webchart.query_1=select deptno, empno, ename, sal from emp
webchart.express_1=rank|x|rnk::sal|deptno
webchart.filter_1=3.5-x|rank
webchart.sort_1=deptno,rank
webchart.group_1=1
If the result of filter express for specific row is negative, the specific row is removed from the result set. For example, if the salary rank is large than 3, the result will be negative number, and the employee record is removed by the program, and just keep the top 3 employees for every department as we required, and we will get a result page as following.
deptno empno ename sal rank 10 7839 KING 5000.0 1 7782 CLARK 2450.0 2 7934 MILLER 1300.0 3 20 7788 SSCOTT 3000.0 1 7902 FORD 3000.0 2 7566 JONESS 2975.0 3 30 7698 BLAKE 2850.0 1 7499 ALLEN 1600.0 2 7844 TURNER 1500.0 3
By doing the computation at application side, we make the SQL very simple. If the report is accessed very frequently, we also reduce the load of database server.