Simple analysis of First_Value function in SqlServer2012

        First_Value returns the value of the first piece of data in a column in the result set. It is the same as the TOP 1 effect. A simple function
First test code
DECLARE @TestData TABLE( ID INT IDENTITY(1,1), Department VARCHAR(20), LastName VARCHAR(20), Rate FLOAT ) INSERT INTO @TestData(Department,LastName,Rate) SELECT 'Document Control','Arifin',17.7885 UNION ALL SELECT 'Document Control','Norred',16.8269 UNION ALL SELECT 'Document Control', 'Kharatishvili', 16.8269 UNION ALL SELECT 'Information Services', 'Chai', 10.25 UNION ALL SELECT 'Information Services', 'Berge', 10.25 UNION ALL SELECT 'Information Services', 'Trenary', 50.4808 UNION ALL SELECT 'Information Services', 'Conroy', 39.6635 UNION ALL SELECT 'Information Services', 'Ajenstat', 38.4615 UNION ALL SELECT 'Information Services', 'Wilson', 38.4615 UNION ALL SELECT 'Information Services', 'Connelly ',32.4519 UNION ALL SELECT 'Information Services', 'Meyyappan', 32.4519 SELECT * FROM @TestData
below using the FIRST_VALUE function to create a new column, return The result is the LastName value of the first line. This so-called first line is affected by the ORDER BY in the OVER. Look at the picture and the code:
Take the ID positive order

Take the ID in reverse order
< Br> If the PARTITION partition function is used in the SQL script, FIRST_VALUE returns the first data value in each partition, see the demo
here with the Department partition, then the entire data set is divided into two parts: Information Services and Document Control Two blocks, at this time FIRST_VALUE returns the first data value in the two partitions, the same is affected by the ORDER BY keyword,

look at an example affected by ORDER BY

Another FIRST_VALUE function appears at the same time, SECOND_VALUE? NO, there is no such function, but there is a LAST_VALUE, how to use the function, do not intend to start a separate article, LAST_VALUE 嗯
zh-CN"],null,[0.93455553],zh-CN"]]]