SQL FIRST_VALUE Function

The FIRST_VALUE function returns the first value in a result set.

Multiple groups can be created by partioning the result set.

In that case, FIRST_VALUE returns the first value inside each group.

Example

#

List products with pricing. In each row, add lowest and highest prices.

SELECT ProductName, 
       FIRST_VALUE(UnitPrice) OVER (ORDER BY UnitPrice) AS Low,
       UnitPrice AS Price,   
       FIRST_VALUE(UnitPrice) OVER (ORDER BY UnitPrice DESC) AS High
  FROM Product

Unit prices appear between the lowest and the highest prices.

Result:  78 records
ProductName Low Price High
Côte de Blaye 2.50 263.50 263.50
Thüringer Rostbratwurst 2.50 123.79 263.50
Mishi Kobe Niku 2.50 97.00 263.50
Sir Rodney's Marmalade 2.50 81.00 263.50
Carnarvon Tigers 2.50 62.50 263.50

Note: By sorting in DESC order, the FIRST_VALUE acts like a LAST_VALUE function.


Syntax

#

Syntax of the FIRST_VALUE function.

FIRST_VALUE(scalar-expression) 
  OVER( PARTITION BY expression, ...
        ORDER BY expression, ... )

scalar-expression -- a column, subquery, or expression evaluated into single value.

PARTITION BY expression, ... -- optional. Organizes rows into different groups where the FIRST_VALUE will be applied. If not specified, the entire result set represents a single group.

ORDER BY expression -- sets the sort order of the result set.

More Examples

FIRST_VALUE with PARTITIOIN

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List the orders for the year 2013. Include the lowest and highest sales amount for each month.
SELECT MONTH(OrderDate) AS Month,
       CONCAT(FirstName, ' ', LastName) AS Customer, 
       FIRST_VALUE(TotalAmount) OVER(PARTITION BY MONTH(OrderDate)
                        ORDER BY TotalAmount) AS 'Lowest Monthly Sale',
       TotalAmount, 
       FIRST_VALUE(TotalAmount) OVER(PARTITION BY MONTH(OrderDate)
                        ORDER BY TotalAmount DESC) AS 'Highest Monthly Sale'
  FROM [Order] O
  JOIN Customer C ON O.CustomerId = C.Id
 WHERE YEAR(OrderDate) = 2013
Result:  408 records
Month Customer Lowest Monthly Sale TotalAmount Highest Monthly Sale
1 Jean Fresnière 49.80 11493.20 11493.20
1 Jytte Petersen 49.80 11283.20 11493.20
1 Roland Mendel 49.80 5796.00 11493.20
2, 3, 4, ... 11
12 Pascale Cartrain 12.50 28.00 6984.50
12 Paolo Accorti 12.50 18.40 6984.50
12 Patricio Simpson 12.50 12.50 6984.50

You may also like



Guides


vsn 3.1