Question 21782 in Mysql Question posted in
The official MySQL documentation can be found here.

finding max price with date – Mysql

I am new to SQL.
in my query, I need max closeprice(column-name) with date (column-name). every month of that year.
I created this query:

Select max(close), date from commodity where commodity ='rice' and location= 'pune' year(date) ='2022' group by month(date) order by month(date) desc;

in this query, I got a max close price but I am not getting the actual max closeprice date. I am getting a max date with a max close price.
example

I want this: Actual max close price with that date and max close price

 date         close     
 2022-10-15   5600
 2022-09-11   6200

I am getting is: max date and max close pice

 date         close     
 2022-10-31   5600
 2022-09-30   6200
Tags:

2

Answers


  1. 0

    Try this:

    select date ,close
from commodity c
join
    (Select max(close) monthly_mx, month(date) mon 
    from commodity 
    where commodity ='rice' and location= 'pune' and year(date) ='2022' 
    group by month(date) ) t
on c.close =monthly_mx and month(c.date)=mon
where c.commodity ='rice' and c.location= 'pune' and year(c.date) ='2022' 
order by month(date) desc;
    Login or Signup to reply.
  2. 0

    A Simple GROUP BY on date column will help you to get such data

    select date ,close ,  MAX(price) as maxPrice
from commodity
where commodity ='rice' and location= 'pune' and year(date) ='2022' 
GROUP BY test_one.date 
order by month(date) desc;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search