SELECT MAX query issue interval

I want to get the highest value in the last 1 day, but the query is not working. (date / time columns formatted by date). The data I want to query is in multiple headers.

SELECT sign, date, price, MAX(price) as maxp FROM table WHERE datetime >= NOW() - INTERVAL 1 DAY

my full query;

$que = $conn->mysql_query("SELECT sign, date, MAX(price) as maxp FROM table WHERE date >= (NOW() - INTERVAL 1 DAY) group by sign, date"); 

$row = mysql_fetch_array($que); { 

?>

<?php
foreach ($que as $row) {
$newQuery = "UPDATE tableb SET `daymax`='".$row["maxp"]."' where `sign`='".$row["sign"]."' ";
mysqli_multi_query($conn, $newQuery)

?>

Answer

If you want sign,date wise max price you need to group by on sign,date. Try this.

SELECT sign, date, MAX(price) as maxp 
FROM table WHERE datetime >= NOW() - INTERVAL 1 DAY
group by sign,date

Leave a Reply

Your email address will not be published. Required fields are marked *