Cách sử dụng hàm FIRST_VALUE và LAST_VALUE

Xét ví dụ sau: Lấy danh sách nhân viên cho biết với mỗi nhóm công việc thì mức lương tương ứng của nhân viên trong nhóm công việc đó là bao nhiêu kèm theo mức lương cao nhất và thấp nhất của nhóm đó.

Để hiểu rõ hơn về yêu cầu trên, ta nhìn vào bảng kết quả sau đây:

ENAME	JOB	SAL	Lowest	Highest
FORD	ANALYST	3000	3000	3000
SCOTT	ANALYST	3000	3000	3000
SMITH	CLERK	800	800	1300
JAMES	CLERK	950	800	1300
ADAMS	CLERK	1100	800	1300
MILLER	CLERK	1300	800	1300
CLARK	MANAGER	2450	2450	2975
BLAKE	MANAGER	2850	2450	2975
JONES	MANAGER	2975	2450	2975
KING	PRESIDENT	5000	5000	5000
MARTIN	SALESMAN	1250	1250	1600
WARD	SALESMAN	1250	1250	1600
TURNER	SALESMAN	1500	1250	1600
ALLEN	SALESMAN	1600	1250	1600

Và đây là kết quả câu SELECT:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SELECT ename, job, sal, FIRST_VALUE ( sal) OVER (PARTITION BY job ORDER BY sal ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "Lowest", LAST_VALUE ( sal) OVER (PARTITION BY job ORDER BY sal ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "Highest" FROM emp;

Một vài lưu ý cho ví dụ trên như sau:

• Hàm FIRST_VALUE: hàm này cho phép ta lấy ra giá trị đầu tiên của 1 tập đã được sắp xếp. Như ví dụ trên đây, sau khi PARTITION thành từng nhóm theo JOB và sắp xếp theo SAL thì ta lấy ra giá trị đầu tiên của tập này. Như nhóm SALESMAN thì FIRST_VALUE sẽ là 1250
• Hàm LAST_VALUE: hàm này cho phép ta lấy ra giá trị cuối cùng của 1 tập đã được sắp xếp. Như ví dụ trên đây, sau khi PARTITION thành từng nhóm theo JOB và sắp xếp theo SAL thì ta lấy ra giá trị cuối cùng của tập này. Như nhóm SALESMAN thì LAST_VALUE sẽ là 1600
• Hàm FIRST_VALUE và LAST_VALUE có thể được dùng kèm với từ khóa IGNORE NULLS. Từ khóa này cho phép ta loại bỏ giá trị NULL nếu như kết quả trả về có giá trị NULL. Cách dùng như sau:
  FIRST_VALUE/LAST_VALUE (column_name) IGNORE NULLS OVER(...)

Kết quả câu lệnh trên sẽ thay đổi nếu như ta thay đổi điều kiện của PRECEDING và FOLLOWING. Xét ví dụ sau: Lấy danh sách nhân viên nhóm theo từng loại công việc và cho biết trong cùng 1 nhóm thì lương của nhân viên đó và lương của người trước đó chênh lệch bao nhiêu.
Đây là bảng kết quả của yêu cầu trên:

ENAME	JOB	SAL	PRE_SAL	DIFFERENCE
FORD	ANALYST	3000	3000	0
SCOTT	ANALYST	3000	3000	0
SMITH	CLERK	800	800	0
JAMES	CLERK	950	800	150
ADAMS	CLERK	1100	950	150
MILLER	CLERK	1300	1100	200
CLARK	MANAGER	2450	2450	0
BLAKE	MANAGER	2850	2450	400
JONES	MANAGER	2975	2850	125
KING	PRESIDENT	5000	5000	0
MARTIN	SALESMAN	1250	1250	0
WARD	SALESMAN	1250	1250	0
TURNER	SALESMAN	1500	1250	250
ALLEN	SALESMAN	1600	1500	100

Và đây là câu SELECT cho bảng kết quả trên:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SELECT ename, job, sal, FIRST_VALUE ( sal) OVER (PARTITION BY job ORDER BY sal ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS "PRE_SAL", sal - FIRST_VALUE ( sal) OVER (PARTITION BY job ORDER BY sal ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS "DIFFERENCE" FROM emp