Geek Answers Handbook

SQL values ​​for min and max rows on the same row

I have a table with price changes, and I need to get the starting price and the latest price. In other words, I want to display the price values ​​for min (StartDate) and max (StartDate) on the same line for each product.

The table structure is simple:

ProductID, StartDate, Price

Desired Result

 ProductId, StartDate, InitialPrice, LatestDate, LatestPrice
+2
source share
2 answers
 WITH latestPrice AS ( SELECT ProductID, StartDate, Price, ROW_NUMBER() OVER (PArtition BY ProductID ORDER BY StartDate DESC) rn FROM TableName ) , initalPrice AS ( SELECT ProductID, StartDate, Price, ROW_NUMBER() OVER (PArtition BY ProductID ORDER BY StartDate ASC) rn FROM TableName ) SELECT a.ProductID, b.StartDate, b.Price InitalPrice, c.StartDate LatestDate, c.Price LatestPrice FROM (SELECT DISTINCT ProductID FROM tableName) a INNER JOIN initalPrice b ON a.ProductID = b.ProductID AND b.rn = 1 INNER JOIN latestprice c ON a.ProductID = c.ProductID AND c.rn = 1
+4
source 
 (SELECT x.ProductId, x.MinStartDate, x.MinPrice, y.MaxStartDate, y.MaxPrice FROM (SELECT a.ProductId, a.MinStartDate, b.Price AS MinPrice FROM (SELECT ProductId, MIN(StartDate) AS MinStartDate FROM Products GROUP BY ProductId) a INNER JOIN Products b ON a.ProductId = b.ProductId AND a.MinStartDate = b.StartDate) x INNER JOIN (SELECT a.ProductId, a.MaxStartDate, b.Price AS MaxPrice FROM (SELECT ProductId, MAX(StartDate) AS MaxStartDate FROM Products GROUP BY ProductId) a INNER JOIN Products b ON a.ProductId = b.ProductId AND a.MaxStartDate = b.StartDate) y ON x.ProductId = y.ProductId

DISCLAIMER: This is from memory, so I apologize if this is not entirely accurate. And he assumes that StartDate is a datetime or not repeated for each productId.

Hope this helps.

0
source

More articles:


All Articles