توضیحات
این تابع مقدار اولین سطر بر اساس هر گروه را برمیگرداند.دستور
تابع FIRST_VALUE به صورت زیر نوشته میشود:|
1
2
3 |
FIRST_VALUE (expression) [RESPECT NULLS | IGNORE NULLS] OVER ([query_partition_clause] [order_by_clause [windowing_clause]]) |
مثال
مثالی از خروجی تابع FIRST_VALUE را مشاهده میکنیم:|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33 |
EMPLOYEE_ID LAST_NAME FIRST_NAME SALARY DEPT_ID----------- --------- ---------- ------ ------- 1000 Jackson Joe 2000 10 2000 Smith Jane 3500 10 3000 Ferguson Samantha 1900 10 4000 Reynolds Allen 4000 20 5000 Anderson Paige 3250 20 6000 Johnson Derek 2750 20 7000 Nelson Sarah 5000 30 8000 Burke Russell 1500 30SELECT DISTINCT FIRST_VALUE(salary) OVER (ORDER BY salary DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "HIGHEST"FROM employees;HIGHEST------- 5000SELECT DISTINCT dept_id, FIRST_VALUE(salary) OVER (PARTITION BY dept_id ORDER BY salary DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "HIGHEST"FROM employeesWHERE dept_id in (10,20)ORDER BY dept_id;DEPT_ID HIGHEST------- ------- 10 3500 20 4000 |