FIRST_VALUE(Transact-SQL)FIRST_VALUE (Transact-SQL)

적용 대상: 예SQL Server(2012부터) 예Azure SQL Database 예Azure SQL Data Warehouse 예병렬 데이터 웨어하우스 APPLIES TO: yesSQL Server (starting with 2012) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

SQL Server 2017SQL Server 2017에서 정렬된 값 집합의 첫 번째 값을 반환합니다.Returns the first value in an ordered set of values in SQL Server 2017SQL Server 2017.

항목 링크 아이콘 Transact-SQL 구문 규칙Topic link icon Transact-SQL Syntax Conventions

구문Syntax

FIRST_VALUE ( [scalar_expression ] )   
    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )  

인수Arguments

scalar_expressionscalar_expression
반환할 값입니다.Is the value to be returned. scalar_expression은 열, 하위 쿼리 또는 결과로 반환되는 값이 하나뿐인 임의의 다른 식일 수 있습니다.scalar_expression can be a column, subquery, or other arbitrary expression that results in a single value. 다른 분석 함수는 사용할 수 없습니다.Other analytic functions are not permitted.

OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )
partition_by_clause는 FROM 절이 생성한 결과 집합을 함수가 적용되는 파티션으로 나눕니다.partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. 지정하지 않을 경우 쿼리 결과 집합의 모든 행이 단일 그룹으로 취급됩니다.If not specified, the function treats all rows of the query result set as a single group. order_by_clause는 작업이 수행되는 논리적 순서를 결정합니다.order_by_clause determines the logical order in which the operation is performed. order_by_clause가 필요합니다.order_by_clause is required. rows_range_clause는 시작점 및 끝점을 지정하여 파티션 내에서 행을 추가로 제한합니다.rows_range_clause further limits the rows within the partition by specifying start and end points. 자세한 내용은 OVER 절(Transact-SQL)을 참조하세요.For more information, see OVER Clause (Transact-SQL).

반환 형식Return Types

scalar_expression과 같은 유형입니다.Is the same type as scalar_expression.

일반적인 주의 사항General Remarks

FIRST_VALUE는 비결정적입니다.FIRST_VALUE is nondeterministic. 자세한 내용은 Deterministic and Nondeterministic Functions을 참조하세요.For more information, see Deterministic and Nondeterministic Functions.

Examples

1.A. 쿼리 결과 집합에 FIRST_VALUE 사용Using FIRST_VALUE over a query result set

다음 예에서는 FIRST_VALUE를 사용하여 지정된 제품 범주에서 가격이 가장 저렴한 제품의 이름을 반환합니다.The following example uses FIRST_VALUE to return the name of the product that is the least expensive in a given product category.

USE AdventureWorks2012;  
GO  
SELECT Name, ListPrice,   
       FIRST_VALUE(Name) OVER (ORDER BY ListPrice ASC) AS LeastExpensive   
FROM Production.Product  
WHERE ProductSubcategoryID = 37;  

결과 집합은 다음과 같습니다.Here is the result set.


Name                    ListPrice             LeastExpensive  
----------------------- --------------------- --------------------  
Patch Kit/8 Patches     2.29                  Patch Kit/8 Patches  
Road Tire Tube          3.99                  Patch Kit/8 Patches  
Touring Tire Tube       4.99                  Patch Kit/8 Patches  
Mountain Tire Tube      4.99                  Patch Kit/8 Patches  
LL Road Tire            21.49                 Patch Kit/8 Patches  
ML Road Tire            24.99                 Patch Kit/8 Patches  
LL Mountain Tire        24.99                 Patch Kit/8 Patches  
Touring Tire            28.99                 Patch Kit/8 Patches  
ML Mountain Tire        29.99                 Patch Kit/8 Patches  
HL Road Tire            32.60                 Patch Kit/8 Patches  
HL Mountain Tire        35.00                 Patch Kit/8 Patches  

2.B. 파티션에 FIRST_VALUE 사용Using FIRST_VALUE over partitions

다음 예에서는 FIRST_VALUE를 사용하여 직함이 같은 다른 직원과 비교해 휴가 일 수가 가장 적은 직원을 반환합니다.The following example uses FIRST_VALUE to return the employee with the fewest number of vacation hours compared to other employees with the same job title. PARTITION BY 절은 직원을 직함별로 분할하며 FIRST_VALUE 함수는 각 파티션에 개별적으로 적용됩니다.The PARTITION BY clause partitions the employees by job title and the FIRST_VALUE function is applied to each partition independently. OVER 절에 지정된 ORDER BY 절은 FIRST_VALUE 함수가 각 파티션의 행에 적용되는 논리적 순서를 결정합니다.The ORDER BY clause specified in the OVER clause determines the logical order in which the FIRST_VALUE function is applied to the rows in each partition. ROWS UNBOUNDED PRECEDING 절은 창 시작점을 각 파티션의 첫 번째 행으로 지정합니다.The ROWS UNBOUNDED PRECEDING clause specifies the starting point of the window is the first row of each partition.

USE AdventureWorks2012;   
GO  
SELECT JobTitle, LastName, VacationHours,   
       FIRST_VALUE(LastName) OVER (PARTITION BY JobTitle   
                                   ORDER BY VacationHours ASC  
                                   ROWS UNBOUNDED PRECEDING  
                                  ) AS FewestVacationHours  
FROM HumanResources.Employee AS e  
INNER JOIN Person.Person AS p   
    ON e.BusinessEntityID = p.BusinessEntityID  
ORDER BY JobTitle;  

다음은 결과 집합의 일부입니다.Here is a partial result set.


JobTitle                            LastName                  VacationHours FewestVacationHours  
----------------------------------- ------------------------- ------------- -------------------  
Accountant                          Moreland                  58            Moreland  
Accountant                          Seamans                   59            Moreland  
Accounts Manager                    Liu                       57            Liu  
Accounts Payable Specialist         Tomic                     63            Tomic  
Accounts Payable Specialist         Sheperdigian              64            Tomic  
Accounts Receivable Specialist      Poe                       60            Poe  
Accounts Receivable Specialist      Spoon                     61            Poe  
Accounts Receivable Specialist      Walton                    62            Poe  

참고 항목See Also

OVER 절(Transact-SQL)OVER Clause (Transact-SQL)