I want to find MAX value by the code and my data like below:
| id | date | code | price |
|---|---|---|---|
| 74 | 2022-01-04 | B | 64 |
| 91 | 2022-01-07 | A | 174 |
| 112 | 2022-01-11 | B | 128 |
| 245 | 2022-01-12 | C | 841 |
| 550 | 2022-01-14 | A | 79 |
| 780 | 2022-01-20 | B | 55 |
| 821 | 2022-01-23 | D | 45 |
| 868 | 2022-01-28 | C | 50 |
| 890 | 2022-02-02 | B | 467 |
| 891 | 2022-02-03 | D | 58 |
| 892 | 2022-02-04 | A | 472 |
What I expect, it will return like below:
| id | date | code | price |
|---|---|---|---|
| 245 | 2022-01-12 | C | 841 |
| 890 | 2022-02-02 | B | 467 |
| 891 | 2022-02-03 | D | 58 |
| 892 | 2022-02-04 | A | 472 |
I'm using below query:
select x.id, x.date, x.code, y.yprice
from data AS x
inner join
(
select id, date, code, MAX(price) AS yprice
from data
group by code
) y
on x.id = y.id AND x.code = y.code
and give me below result:
About the result: The value of MAX is right, however the id and date is wrong.
Any idea to fix the query?
Thank You...
CodePudding user response：
SELECT X.ID,X.DATE,X.CODE,X.PRICE
FROM
(
SELECT C.id,C.date,C.code,C.price,
ROW_NUMBER()OVER(PARTITION BY C.code ORDER BY C.Price DESC)XCOL
FROM test AS C
)X WHERE X.XCOL=1
Could you please try this one