-3

I have 2 queries. I want to get MAX date. If I get MAX date my subquery will look like below.

Query 1:

select
   tb_format.format 
from
   tb_format 
where
   tb_format.id not in 
   (
      select
         penyakit.format 
      from
         penyakit 
      where
         penyakit.id_puskesmas =$ id 
         AND MONTH(penyakit.waktu_upload) = MONTH(CURRENT_DATE)
   )

I want to add MAX on this MONTH(penyakit.waktu_upload).

Query 2:

select
   tb_format.format 
from
   tb_format 
where
   tb_format.id not in 
   (
      select
         penyakit.format 
      from
         penyakit 
      where
         penyakit.id_puskesmas =$ id 
         AND MAX(MONTH(penyakit.waktu_upload)) = MONTH(CURRENT_DATE) -- HERE
   )

However, I'm getting an error:

Screenshot 1

Screenshot 2

kit
  • 1,166
  • 5
  • 16
  • 23
Adhik Mulat
  • 5
  • 5
  • 1
    Please add some sample data, and expected output. Add details about what are you trying to achieve logically. Based on that information, query can be reformulated in more effective manner. Please go through this link once: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/q/333952/2469308) – Madhur Bhaiya Nov 17 '18 at 07:14
  • Please show the code and/or state the errors. The text on the picture is too small for some people to read. In addition, the text on the image cannot be indexed by search engines for future visitors. – jww Nov 17 '18 at 07:33

1 Answers1

0

You can't have MAX in a WHERE clause, you can have it in a HAVING clause (this explains the difference a bit more)...

  select
     penyakit.format 
  from
     penyakit 
  where
     penyakit.id_puskesmas =$ id 
  group by 
     penyakit.format
  having
     MAX(MONTH(penyakit.waktu_upload)) = MONTH(CURRENT_DATE)

There has to be a GROUP BY clause and this gives the basis for what the MAX operates over, the HAVING clause is almost a WHERE clause for the GROUP BY.

Not sure what the $ id is so I've just copied from your original code, but this will need to be corrected.

Community
  • 1
  • 1
Nigel Ren
  • 56,122
  • 11
  • 43
  • 55