SQL FIRST_VALUE Function

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.

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 of the FIRST_VALUE function.

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

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

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

• Our Sql Window Functions Reference
• Our Sql LAST_VALUE Function Reference
• Our Sql LEAD Function Reference