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

JavaScript
​x
 
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)

?>

JavaScript
 
$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); { ​?>​<?phpforeach ($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

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

Advertisement

Answer