SQL get corresponding data where min or max value-CodePudding

Here is my data structure

ID_group	Date	Price
1	20/11/2022	3
1	19/11/2022	4
2	18/11/2022	42
2	19/11/2022	2
1	21/11/2022	2

I want to make a table in order to get my data in this format :

ID_group	MaxPrice	MaxPriceDate	MinPrice	MinPriceDate
1	4	19/11/2022	3	20/11/2022
2	42	18/11/2022	2	19/11/2022

Here is what I have now :

select ID_group,
max(price) MaxPrice,
'' MaxPriceDate,
min(price) MinPrice,
'' MinPriceDate
from table
group by ID_group

CodePudding user response：

We can use ROW_NUMBER here, along with pivoting logic:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY ID_group ORDER BY Price) rn1,
                ROW_NUMBER() OVER (PARTITION BY ID_group ORDER BY Price DESC) rn2
    FROM yourTable t
)

SELECT
    ID_group,
    MAX(CASE WHEN rn2 = 1 THEN Price END) AS MaxPrice,
    MAX(CASE WHEN rn2 = 1 THEN "Date" END) AS MaxPriceDate,
    MAX(CASE WHEN rn1 = 1 THEN Price END) AS MinPrice,
    MAX(CASE WHEN rn1 = 1 THEN "Date" END) AS MinPriceDate
FROM cte
GROUP BY ID_group
ORDER BY ID_group;