Select most recent value in a group

Question

I'd like to add the column LastSalePrice to the query below:

SELECT 
    P.SKU, 
    C.TotalSales,
    MIN(C.MinPriceChannel) OVER(PARTITION BY P.SKU) AS MinPrice,
    MAX(C.MaxPriceChannel) OVER(PARTITION BY P.SKU) AS MaxPrice,
    P.ProductName, 
    C.SalesChannel,
    C.Sales, 
    C.MinPriceChannel, 
    C.MaxPriceChannel
    --C.LastSalePrice
FROM
(
    SELECT 
        P.ProductId
        ,SUM(COUNT(*)) OVER(PARTITION BY P.ProductId) AS TotalSales
        ,COUNT(*) AS Sales
        ,MIN(OI.UnitPrice) AS MinPriceChannel
        ,MAX(OI.UnitPrice) AS MaxPriceChannel
        ,O.SalesChannel
        --LAST_VALUE(OI.UnitPrice) OVER (PARTITION BY P.ProductId ORDER BY O.SalesDate) as LastSalePrice
    FROM Product P
    JOIN OrderItem OI ON OI.ProductId = P.ProductId
    JOIN Orders O ON O.OrderId = OI.OrderId
    WHERE 
        O.SalesDate >= DATEADD(YEAR, -1, GETDATE())
    GROUP BY 
        P.ProductId,
        O.SalesChannel
) C
JOIN Product P ON P.ProductId = C.ProductId
ORDER BY P.ProductName ASC, C.SalesChannel ASC

I left commented what I have tried - using last_value function - but it says that I can't order by O.SalesDate because it's not contained in an aggregation function.

How can I select the last sale price for each product and each sales channel?

Expected output:

SKU	TotalSales	MinPrice	MaxPrice	ProductName	SalesChannel	Sales	MinPriceChannel	MaxPriceChannel	LastSalePrice
0002	9	12.42	14.99	Canned Unicorn Meat	eGulf	3	12.42	13.99	13.99
0002	9	12.42	14.99	Canned Unicorn Meat	Kasim	3	12.72	14.95	12.72
0002	9	12.42	14.99	Canned Unicorn Meat	Nile	3	12.99	14.99	14.99
0001	9	43.99	50.00	StackOverflow Keyboard	eGulf	3	46.60	49.75	46.60
0001	9	43.99	50.00	StackOverflow Keyboard	Kasim	3	43.99	50.00	48.99
0001	9	43.99	50.00	StackOverflow Keyboard	Nile	3	44.99	49.99	47.99

Table definition and sample data

CREATE TABLE Product 
(
    ProductId int NOT NULL PRIMARY KEY IDENTITY(1,1),
    ProductName varchar (255) NOT NULL,
    SKU varchar(30) NOT NULL
)
GO

CREATE TABLE Orders 
(
    OrderId int NOT NULL PRIMARY KEY IDENTITY(1, 1),
    SalesDate datetime2 NOT NULL default(GETDATE()),
    SalesChannel varchar(30) NOT NULL
)
GO

CREATE TABLE OrderItem 
(
    OrderItemId int NOT NULL PRIMARY KEY IDENTITY(1, 1),
    ProductId int FOREIGN KEY REFERENCES Product(ProductId),
    OrderId int FOREIGN KEY REFERENCES Orders(OrderId),
    UnitPrice decimal(12, 2) NOT NULL
)

GO
INSERT INTO Product (ProductName, SKU) 
VALUES ('StackOverflow Keyboard', '0001'), 
       ('Canned Unicorn Meat', '0002');
GO

INSERT INTO Orders (SalesDate, SalesChannel) 
VALUES ('2021-04-08', 'Nile'), ('2021-04-09', 'Nile'), ('2021-04-10',  'Nile'),
       ('2021-04-11', 'Nile'), ('2021-04-12', 'Nile'), ('2021-04-13', 'Nile'),
       ('2021-04-08', 'Kasim'), ('2021-04-09', 'Kasim'), ('2021-04-10', 'Kasim'),
       ('2021-04-11', 'Kasim'), ('2021-04-12', 'Kasim'), ('2021-04-13', 'Kasim'),
       ('2021-04-08', 'eGulf'), ('2021-04-09', 'eGulf'), ('2021-04-10', 'eGulf'),
       ('2021-04-11', 'eGulf'), ('2021-04-12', 'eGulf'), ('2021-04-13', 'eGulf');
GO

INSERT INTO OrderItem (ProductId, OrderId, UnitPrice) 
VALUES (1, 1, 49.99), (1, 2, 44.99), (1, 3, 47.99),
       (2, 4, 12.99), (2, 5, 13.99), (2, 6, 14.99),
       (1, 7, 43.99), (1, 8, 50.00), (1, 9, 48.99),
       (2, 10, 14.95), (2, 11, 13.50), (2, 12, 12.72),
       (1, 13, 47.89), (1, 14, 49.75), (1, 15, 46.60),
       (2, 16, 12.42), (2, 17, 13.59), (2, 18, 13.99);
GO

Serg · Answer 1 · 2021-04-13T06:52:48.713

You can do it with a scalar subquery

WITH src AS(
    SELECT OI.*, O.SalesDate, O.SalesChannel
    FROM OrderItem OI 
    JOIN Orders O ON O.OrderId = OI.OrderId
    WHERE 
        O.SalesDate >= DATEADD(YEAR, -1, GETDATE())
)
SELECT 
    P.SKU, 
    C.TotalSales,
    MIN(C.MinPriceChannel) OVER(PARTITION BY P.SKU) AS MinPrice,
    MAX(C.MaxPriceChannel) OVER(PARTITION BY P.SKU) AS MaxPrice,
    P.ProductName, 
    C.SalesChannel,
    C.Sales, 
    C.MinPriceChannel, 
    C.MaxPriceChannel,
    C.LastSalePrice
FROM
(
    SELECT 
        P.ProductId
        ,SUM(COUNT(*)) OVER(PARTITION BY P.ProductId) AS TotalSales
        ,COUNT(*) AS Sales
        ,MIN(OOI.UnitPrice) AS MinPriceChannel
        ,MAX(OOI.UnitPrice) AS MaxPriceChannel
        ,OOI.SalesChannel
        ,(SELECT TOP 1 z.UnitPrice
           FROM src z 
           WHERE P.ProductId = z.ProductId AND OOI.SalesChannel = z.SalesChannel
           ORDER BY z.SalesDate DESC) as LastSalePrice
    FROM Product P
    JOIN src OOI ON OOI.ProductId = P.ProductId
    GROUP BY 
        P.ProductId,
        OOI.SalesChannel
) C
JOIN Product P ON P.ProductId = C.ProductId
ORDER BY P.ProductName ASC, C.SalesChannel ASC

score 1 · Accepted Answer · answered Apr 13 '21 at 08:13

I had to use FIRST_VALUE, LAST VALUE didn't work for me.

;WITH T1 AS (
    SELECT 
        OI.ProductId
        ,OI.UnitPrice
        ,O.SalesChannel
        ,O.SalesDate
        ,FIRST_VALUE(UnitPrice) OVER (PARTITION BY OI.ProductId, SalesChannel ORDER BY O.SalesDate DESC) as LastSalePrice
    FROM OrderItem OI
    JOIN Orders O ON O.OrderId = OI.OrderId
    WHERE 
        O.SalesDate >= DATEADD(YEAR, -1, GETDATE())
)
SELECT 
    P.SKU, 
    C.TotalSales,
    MIN(C.MinPriceChannel) OVER(PARTITION BY P.SKU) AS MinPrice,
    MAX(C.MaxPriceChannel) OVER(PARTITION BY P.SKU) AS MaxPrice,
    P.ProductName, 
    C.SalesChannel,
    C.Sales, 
    C.MinPriceChannel, 
    C.MaxPriceChannel,
    C.LastSalePrice
FROM
(
    SELECT 
        ProductId
        ,SUM(COUNT(*)) OVER(PARTITION BY ProductId) AS TotalSales
        ,COUNT(*) AS Sales
        ,MIN(UnitPrice) AS MinPriceChannel
        ,MAX(UnitPrice) AS MaxPriceChannel
        ,SalesChannel
        ,MAX(LastSalePrice) AS LastSalePrice
    FROM T1
    GROUP BY 
        ProductId,
        SalesChannel
) C
JOIN Product P ON P.ProductId = C.ProductId
ORDER BY P.ProductName ASC, C.SalesChannel ASC

Select most recent value in a group

2 Answers2