Teradata MIN Window function

MINIMUM Window Function:

The MINIMUM Window function reports the minimum value for the defined group. If we don’t use PARTITION BY then all the rows are considered as a group and it gives minimum value in the entire group. Only difference between the aggregate MINIMUM and MINIMUM window is that with MINIMUM window function we get details rows also. SELECT EMPLOYEEID, DEPARTMENTNO, SALARY, MIN(SALARY) OVER (ORDER BY DEPARTMENTNO ) FROM EMPLOYEE2;   Employeeid DepartmentNo Salary Group Min(Salary) 9 100 9000 1000 1 100 1000 1000 5 100 5000 1000 2 200 2000 1000 10 200 10000 1000 124 200 12345.11 1000 6 200 6000 1000 7 300 7000 1000 11 300 11000 1000 3 300 3000 1000 4 400 4000 1000 8 400 8000 1000 12 400 12000 1000 144 400 12345.11 1000 We can get the same result using the following: SELECT EMPLOYEEID, DEPARTMENTNO, SALARY, MIN(SALARY) OVER (ORDER BY DEPARTMENTNO ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) as minsal  FROM EMPLOYEE2; We can use the same function to find out what percentage of minimum salary is the current salary. Following is how we do that: SELECT EMPLOYEEID, DEPARTMENTNO, SALARY, MIN(SALARY) OVER (ORDER BY DEPARTMENTNO ) as minsal ,(salary/minsal) * 100 FROM EMPLOYEE2; Employeeid DepartmentNo Salary minsal ((Salary/minsal)*100) 1 100 1000 1000 100 5 100 5000 1000 500 9 100 9000 1000 900 2 200 2000 1000 200 6 200 6000 1000 600 10 200 10000 1000 1000 124 200 12345.11 1000 1235 3 300 3000 1000 300 7 300 7000 1000 700 11 300 11000 1000 1100 12 400 12000 1000 1200 4 400 4000 1000 400 144 400 12345.11 1000 1235 8 400 8000 1000 800 If we didn’t not have this technique we would need to calculate the minimum first and store in a volatile table and then use this result to calculate the percentage.

Minimum Window function with Partition:

As explained earlier We can use PARTITION BY clause to produce grouping. Following query provides minimum per department.   SELECT EMPLOYEEID, DEPARTMENTNO, SALARY, MIN(SALARY) OVER (PARTITION BY  DEPARTMENTNO ORDER BY DEPARTMENTNO ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) as minsal  FROM EMPLOYEE2; Employeeid DepartmentNo Salary minsal 1 100 1000 1000 5 100 5000 1000 9 100 9000 1000 2 200 2000 2000 10 200 10000 2000 6 200 6000 2000 124 200 12345.11 2000 3 300 3000 3000 7 300 7000 3000 11 300 11000 3000 12 400 12000 4000 144 400 12345.11 4000 4 400 4000 4000 8 400 8000 4000