SQL Functions (Analytic) FIRST_VALUE
Example
You use the FIRST_VALUE function to determine the first value in an ordered result set, which you identify using a scalar expression.
SELECT StateProvinceID, Name, TaxRate,
FIRST_VALUE(StateProvinceID)
OVER(ORDER BY TaxRate ASC) AS FirstValue
FROM SalesTaxRate;
In this example, the FIRST_VALUE function is used to return the ID of the state or province with the lowest tax rate. The OVER clause is used to order the tax rates to obtain the lowest rate.
| StateProvinceID | Name | TaxRate | FirstValue |
|---|---|---|---|
| 74 | Utah State Sales Tax | 5.00 | 74 |
| 36 | Minnesota State Sales Tax | 6.75 | 74 |
| 30 | Massachusetts State Sales Tax | 7.00 | 74 |
| 1 | Canadian GST | 7.00 | 74 |
| 57 | Canadian GST | 7.00 | 74 |
| 63 | Canadian GST | 7.00 | 74 |
