Subquery max group needs and additional where clause

Databases
1

Hello

I have a discussion forum that I would like to filter approved posts. I would like to know how to select the max(datetime) but only if the max has an approved value of 1. The only way I can think to do this is adding a where clause of approved = 1 but this will not work with a group by. Any suggestions?


 select * from questions where datetime in (select max(datetime) from questions group by id )
2

sure it will

SELECT questions.* 
  FROM ( SELECT id
              , MAX(datetime) AS max_datetime
           FROM questions 
          WHERE approved = 1
         GROUP 
             BY id ) AS m
INNER
  JOIN questions
    ON questions.id = m.id
   AND questions.datetime = m.max_datetime
3

I think this works…? I “grouped by” id,approved and then selected where it is not equal to 1?


SELECT * FROM questions WHERE DATETIME IN (SELECT max(DATETIME) FROM questions GROUP BY id,approved having approved <> 0)